T-SQL Control-Flow Statements

This month, I'll look at seven control-flow statements that you can use in SQL Server's T-SQL batches and stored procedures. These statements let you control the execution flow within a T-SQL batch or stored procedure, and they let you use T-SQL to execute complex programming tasks.

7. GOTO
GOTO is a basic T-SQL control-flow statement. It causes the execution of a T-SQL batch to branch to the label specified in the line with the GOTO statement.

GOTO error_condition

6. IF-ELSE
The IF statement lets you test a variable's contents and conditionally execute the T-SQL statements that follow, depending on the test's results. When the IF test evaluates to false, the optional ELSE portion of the statement lets an alternative T-SQL statement execute.

IF (@@Error  0)
   ROLLBACK
ELSE
   COMMIT
END

5. BEGIN-END
BEGIN-END lets you group T-SQL statements and execute multiple statements as a result of an IF test.

BEGIN
   SET @ErrorNumber = @@ERROR
   PRINT 'Error encountered' 
END

4. WAITFOR
WAITFOR lets you delay the execution of a T-SQL batch either for a given amount of time (when you specify the DELAY keyword) or until a specified system time (when you specify the TIME keyword).

WAITFOR TIME '23:00'
WAIT FOR DELAY ''00:01:00''

3. RETURN
RETURN lets you exit from a T-SQL batch or stored procedure. You can specify an optional integer variable with RETURN to pass a status value to the calling procedure, which can evaluate the return code and perform different actions depending on the results of the T-SQL batch or stored procedure.

RETURN @return_code

2. WHILE-BREAK-CONTINUE
WHILE is a powerful T-SQL control-flow statement. The WHILE statement causes repeated execution of a statement or block of statements while a given condition is true. You can specify the optional BREAK and CONTINUE keywords to exit from the while loop or cause the loop to continue.

WHILE @@FETCH_STATUS = 0
BEGIN
   FETCH NEXT FROM Employee_Cursor
END

1. CASE
CASE provides a structured method of evaluating a list of options and then returning a single value. You can use the CASE statement alone or within a SELECT statement.

SELECT au_fname, au_lname, 
   CASE state
      WHEN 'OR' THEN 'Oregon'
      END AS StateName
FROM authors