Flow Control Language

for RuBoard

Certain Transact-SQL commands affect the order in which statements are executed in a stored procedure or command batch. These are referred to as flow control or control-of-flow statements because they control the flow of Transact-SQL code execution. Transact-SQL flow control language statements include IF ELSE, WHILE, GOTO, RETURN, WAITFOR, BREAK, CONTINUE, and BEGIN END. We'll discuss the various flow control commands further in the book, but for now here's a simple procedure that illustrates all of them (Listing 1-30):

Listing 1-30 Flow control statements as they behave in the wild.

 USE pubs IF OBJECT_ID('dbo.listsales') IS NOT NULL   DROP PROC dbo.listsales GO CREATE PROC dbo.listsales @title_id tid=NULL AS IF (@title_id='/?') GOTO Help -- Here's a basic IF -- Here's one with a BEGIN..END block IF NOT EXISTS(SELECT * FROM titles WHERE title_id=@title_id) BEGIN   PRINT 'Invalid title_id'   WAITFOR DELAY '00:00:03' -- Delay 3 secs to view message   RETURN -1 END IF NOT EXISTS(SELECT * FROM sales WHERE title_id=@title_id) BEGIN   PRINT 'No sales for this title'   WAITFOR DELAY '00:00:03' -- Delay 3 secs to view message   RETURN -2 END DECLARE @qty int, @totalsales int SET @totalsales=0 DECLARE c CURSOR FOR SELECT qty FROM sales WHERE title_id=@title_id OPEN c FETCH c INTO @qty WHILE (@@FETCH_STATUS=0) BEGIN       -- Here's a WHILE loop   IF (@qty<0) BEGIN     Print 'Bad quantity encountered'     BREAK    -- Exit the loop immediately   END ELSE IF (@qty IS NULL) BEGIN     Print 'NULL quantity encountered -- skipping'     FETCH c INTO @qty     CONTINUE -- Continue with the next iteration of the loop   END   SET @totalsales=@totalsales+@qty   FETCH c INTO @qty END CLOSE c DEALLOCATE c SELECT @title_id AS 'TitleID', @totalsales AS 'TotalSales' RETURN 0     -- Return from the procedure indicating success Help: EXEC sp_usage @objectname='listsales',        @desc='Lists the total sales for a title',        @parameters='@title_id="ID of the title you want to check"',        @example='EXEC listsales "PS2091"',        @author='Ken Henderson',        @email='[e-mail]',        @version='1', @revision='0',        @datecreated='19990803', @datelastchanged='19990818' WAITFOR DELAY '00:00:03' -- Delay 3 secs to view message RETURN -1 GO EXEC dbo.listsales 'PS2091' EXEC dbo.listsales 'badone' EXEC dbo.listsales 'PC9999' TitleID TotalSales ------- ----------- PS2091  191 Invalid title_id No sales for this title 

for RuBoard