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.


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                              


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                              


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                              


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''                              


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                              


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                              


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