This chapter is from the book
This chapter is from the book
This chapter is from the book
This chapter is from the book
Flow Control Language
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='khen@khen.com',
@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