Oracle PL/SQL Control-of-Flow T-SQL · Control-of-Flow

T-SQL Control-of-Flow Statements

Master T-SQL control-of-flow statements — BEGIN/END, IF/ELSE, CASE, WHILE, BREAK, CONTINUE, GOTO, RETURN, WAITFOR, and TRY/CATCH. Comprehensive overview of every keyword that turns SQL queries into procedural T-SQL programs.

What is Control-of-Flow?

Control-of-flow is the family of T-SQL statements that decide which code runs and when. Plain SQL queries run top-to-bottom in a fixed order — control-of-flow statements let you branch (run different code based on a condition), loop (run code repeatedly), and jump (transfer execution somewhere else). They turn a sequence of SQL into a real program.

These statements live alongside DML and DDL — you'll find them inside batches, stored procedures, functions, and triggers, wherever conditional or repeated logic is needed.

The Eight Control-of-Flow Keywords

Keyword	Purpose	Used For
`BEGIN ... END`	Group multiple statements as a single block	Pairs with IF, WHILE, TRY etc. to scope multi-statement bodies
`IF ... ELSE`	Conditional branching	Run one block when a condition is true, another (optional) when false
`CASE`	Multi-way value selection (expression, not statement)	Inline conditional values within SELECT, SET, ORDER BY, etc.
`WHILE`	Loop while a condition is true	Repeated work with a stop condition — counters, cursor-style processing
`BREAK`	Exit the innermost WHILE loop	Stop iterating early when a found-it condition triggers
`CONTINUE`	Skip to next iteration of the innermost WHILE	Skip the rest of the current loop body; keep looping
`GOTO`	Unconditional jump to a label	Cleanup paths and (rarely) skip-ahead logic
`RETURN`	Exit a batch / procedure / function	Early-exit; optionally return an integer status code
`WAITFOR`	Pause execution	Delay for a duration, or wait until a clock time
`TRY ... CATCH`	Structured error handling	Catch and react to runtime errors (covered in the Error Handling section)

1. BEGIN ... END

Groups several statements into one block. By itself it does nothing — its job is to be the body of an IF, WHILE, TRY, etc., when that body has more than one statement.

BEGIN -- one or more SQL statements END

2. IF ... ELSE

Runs one block when a condition is TRUE, optionally another when it's FALSE.

IF condition BEGIN -- runs when condition is TRUE END ELSE BEGIN -- runs when condition is FALSE END

3. CASE

Picks a value from several alternatives. Unlike the others, CASE is an expression, not a statement — it goes inside SELECT, SET, ORDER BY, WHERE, anywhere a value can appear.

CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE default_value END

4. WHILE

Runs a block of code over and over while a condition stays TRUE. The condition is re-checked at the top of every iteration.

WHILE condition BEGIN -- statements to repeat END

5. BREAK

Exits the innermost WHILE loop immediately, regardless of the loop's condition. Use it to bail out early when you've found what you needed (or hit an error).

WHILE condition BEGIN IF stop_now BEGIN BREAK; END -- more statements END

6. CONTINUE

Skips the rest of the current iteration and re-evaluates the WHILE condition for the next pass.

WHILE condition BEGIN IF skip_this_one BEGIN CONTINUE; END -- skipped if CONTINUE fires END

⚠️ Pair CONTINUE with the increment. If your loop counter advances after CONTINUE, the same skip-condition fires forever and you have an infinite loop. Always make sure the variable that drives the loop is updated before the CONTINUE.

7. GOTO

Jumps to a labeled point. The label is a name followed by a colon. Most modern code avoids GOTO in favor of structured constructs, but it's still legitimate for cleanup paths and a handful of error-handling scenarios.

GOTO label_name; -- code skipped if GOTO ran label_name: -- execution resumes here

8. RETURN

Stops the current batch, procedure, or function immediately. From a procedure you can optionally return a small integer status code to the caller.

RETURN [integer_expression]

9. WAITFOR

Pauses execution. Two main forms — pause for a duration, or pause until a specific time of day:

Form	Effect
`WAITFOR DELAY 'hh:mm:ss'`	Wait for the specified duration
`WAITFOR TIME 'hh:mm:ss'`	Wait until that time of day arrives
`WAITFOR (RECEIVE ...)`	Advanced — wait for a Service Broker message

⚠️ WAITFOR holds the connection. While paused, the connection's worker thread, locks, and resources stay allocated. Don't use it inside web requests, OLTP code, or anywhere a user is waiting.

Modern Companion — TRY ... CATCH

Strictly speaking TRY ... CATCH is an error-handling construct, not pure flow control — but it's the modern partner to BEGIN/END/IF for any procedure that might fail. We cover it in detail in the Error Handling section, but it's worth knowing it exists alongside these:

BEGIN TRY -- code that might fail END TRY BEGIN CATCH -- runs only if the TRY block raised an error END CATCH

What's Next

The pages that follow drill into each statement with full worked examples and traced output:

BEGIN ... END — block grouping (the foundation for everything else)
IF ... ELSE — conditional branching, including ELSE IF chains and nesting
WHILE — looping, infinite-loop pitfalls, and counter patterns
BREAK — early exit from a loop
CONTINUE — skip the current iteration
GOTO — labels and unconditional jumps
RETURN — exiting from procedures with status codes
WAITFOR — delays and scheduling

Conclusion

Control-of-flow turns a list of SQL statements into a program — branching, looping, jumping, exiting.
BEGIN ... END groups statements; IF ... ELSE branches; WHILE loops; CASE picks a value inline.
BREAK and CONTINUE shape loop behavior; GOTO jumps to labels; RETURN exits a batch or procedure.
WAITFOR pauses execution — useful but expensive; never put it on a request path.
TRY ... CATCH is the structured way to react to errors — covered in its own section.