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