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