Baldwin Apps

Posted on Jun 16

SQL Pattern Series #6: The Routing Pattern

#sql #tutorial #database #beginners

Sometimes SQL decides what happens next

SQL Pattern Series #6 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

How SQL can make decisions
When IF and ELSE become useful
Why stored procedures often contain routing logic
How to think about SQL as a decision engine

Many developers first learn SQL as a query language.

SELECT *
FROM Users;

Retrieve data.

Filter data.

Sort data.

Aggregate data.

But eventually you discover something interesting:

SQL can also decide what happens next.

The Problem

Imagine an application sends a request to a stored procedure.

The action could be:

retrieve users
add a user
delete a user

The database must determine which action to perform.

That means the query is no longer just retrieving data.

It is making a decision.

The Routing Pattern

The Routing Pattern uses conditional logic to direct execution down different paths.

For example:

IF @action = 'GET'
    EXEC get_users;

ELSE IF @action = 'ADD'
    EXEC add_user;

ELSE
    EXEC delete_user;

Note: The example shown uses SQL Server (T-SQL) syntax. Conditional and procedural syntax varies between database systems, but the underlying pattern—routing execution based on a condition—exists in most platforms.

The database evaluates a condition.

Based on the result, execution follows a specific route.

Just like a road sign directs traffic.

Thinking About Routing

Many SQL developers think primarily in terms of:

tables
rows
queries

The Routing Pattern introduces another perspective:

decisions
actions
execution paths

The question changes from:

What data should I retrieve?

to:

What should happen next?

Example

Imagine a user management system.

IF @action = 'GET'
    EXEC get_users;

Retrieve existing users.

ELSE IF @action = 'ADD'
    EXEC add_user;

Create a new user.

ELSE
    EXEC delete_user;

Remove a user.

A single procedure can direct execution to multiple outcomes.

Why This Pattern Matters

Routing logic appears frequently in:

stored procedures
ETL processes
administrative scripts
maintenance jobs
workflow automation

The database often becomes responsible for coordinating actions rather than simply returning data.

A Note on Complexity

Routing logic can be extremely useful.

But it can also become difficult to maintain if too many branches are added.

For example:

IF ...
ELSE IF ...
ELSE IF ...
ELSE IF ...
ELSE IF ...

Large decision trees can become hard to follow.

As routing logic grows, consider whether responsibilities should be split into separate procedures or services.

When I Reach for This Pattern

I typically use the Routing Pattern when:

multiple actions share a common entry point
a stored procedure must support different behaviors
execution depends on user input
workflow decisions belong close to the data

Examples include:

user management procedures
import pipelines
administrative tooling
automation workflows

Key Takeaway

SQL is not limited to retrieving data.

Sometimes it acts as a decision engine.

The Routing Pattern helps answer:

Which action should happen next?

rather than:

Which rows should be returned?

That shift opens up a different way of thinking about database programming.