T-SQL Flow-Control

The T-SQL language supports basic flow-control logic that will allow you to perform program flow and branching according to certain conditions you provide the switching routines. The routines allow you to test one thing or another in simple either/or constructions, or test for multiple values in an easy-to-use CASE facility. The T-SQL flowcontrol options are as follows:

• If…Else

• CASE

• While

• Continue/Break

• GOTO/Return

If…Else

This branching or condition-switching statement will execute an isolated block of code in a routine according to a qualifying condition. If the condition qualifies, the code in the If block is executed. If it does not qualify, the program moves to the block of code in the Else section of the routine. The block of code in the Else section can contain something of substance or very little.

The syntax of this statement is as follows:

 IF condition  Begin    {do something here}  End Else  Begin    {do something here}  End

This syntax is a little like Pascal; however, notice that no “end ifs” are required, but you should enclose your code in the Begin…End blocks. I say “should” because you can get away with omitting the Begin…End blocks in simple code segments. However, the Begin...End is essential when you need to make sure that all lines in the code segment are processed.

CASE

The CASE statement works the same as the CASE statements you find in all modern programming languages such as Visual Basic, or Delphi or Java. The T-SQL CASE statement can compare a variable or a field against several variables or fields. You could technically do this with multiple If…Else blocks, but that would be ugly to say the least, and you have no way to escape such a construction after a condition finds a match or tests true.

T-SQL CASE statements test a variable to be true by using the WHEN…THEN clause. For example, “WHEN the banana is yellow” THEN “eat it.” After the WHEN tests true, the THEN condition is applied and execution flow continues through the CASE block. For example, the statement

 SELECT ProductName AS 'Products', 'Popularity'= CASE   WHEN UnitsInStock <= 5 THEN 'Fast Mover'   WHEN UnitsInStock > 5 AND UnitsInStock <= 15 THEN 'Average Mover'   WHEN UnitsInStock > 15 AND UnitsInStock <= 1000 THEN 'Slow Mover' END FROM Products

returns the following table:

 Chai                          Slow Mover Chang                         Slow Mover Aniseed Syrup                 Average Mover Chef Anton's Cajun Seasoning  Slow Mover Chef Anton's Gumbo Mix        Fast Mover Grandma's Boysenberry Spread  Slow Mover Uncle Bob's Organic Dried P.. Average Mover Northwoods Cranberry Sauce    Average Mover Mishi Kobe Niku               Slow Mover ...

Obviously the preceding statement might make more sense if the query also checked restock dates and other factors because an item could be considered a slow mover an hour after a new shipment arrived. However, it adequately illustrates a simple CASE usage.

You can do a lot with CASE, such as assign the obtained value in a case statement and then pass that out to a stored procedure or another construction. For example, consider the following statement:

 DECLARE @Discount real DECLARE @CouponCode char(5) SET @CouponCode = 'CDKIG' SET @Discount = CASE @CouponCode   WHEN 'CXDFR' THEN 10   WHEN 'CDKIG' THEN 7.5   WHEN 'CKIDK' THEN 8 END PRINT @Discount

I use the discount variable obtained at the end of the CASE and apply it to an item for which the customer has a discount coupon I can identify with a coupon code. In this case, the discount is 7.5 percent. The variable @CouponCode could change from item to item. This can be wrapped up in a trigger, as demonstrated in the next chapter, allowing the server to appropriately apply the discount.

WHILE

The WHILE loop is a flow-control statement that executes a single statement or block of code between BEGIN and END keywords. For example, the following is a simple WHILE statement that increments a value:

 DECLARE @Count smallint SET @Count = 0 WHILE @Count < 10   SET @Count = @Count + 1

To repeatedly execute more than just a single line of code, enclose the code between the BEGIN and END blocks, as demonstrated in If…Else. We will revisit WHILE in later chapters to demonstrate some advanced T-SQL features, such as triggers, cursors, and stored procedures.

Continue or Break

Use CONTINUE and BREAK to change or stop the execution of the WHILE loop. The CONTINUE keyword restarts a WHILE loop, and the BREAK terminates the innermost loop it is in.

GOTO and RETURN

These two flow-control statements let you jump out of your current segment and move to another location in the procedure, similar to the GOTO in VB or DBase. GOTO moves to a line identified by a label followed by a colon (ArrivedHere:). RETURN ends a procedure unconditionally and can optionally return a result.

The GOTO command is confined to a control-of-flow statement, statement blocks, or procedures, but it cannot go to a label outside of the current process. However, the GOTO branch can alter the flow and reroute it to a label defined before or after GOTO. The following example emulates a WHILE loop, and the RETURN is used to break out of the loop when a certain value is reached:

 DECLARE @Counter int SET @Counter = 0 Counter:     SET @Counter = @Counter + 1       GOTO CheckResult CheckResult:  IF @Counter = 10   BEGIN    PRINT 'You have reached '+ CAST (@Counter AS CHAR)    RETURN   END  Else   Goto Counter

WAITFOR

The WAITFOR statement suspends procedure execution until a certain time or time interval has passed. The following example prints the time exactly as prescribed in the argument, but notice the conversion and trimming that is needed to return the system time in a simple time format of 00:00 hours:

 BEGIN WAITFOR TIME '18:20'   PRINT 'THE TIME IS '+ LEFT (CONVERT (CHAR(20), GETDATE(), 14), 5) END THE TIME IS 18:20

RAISERROR

RAISERROR is a facility supported by SQL Server 2005 as a flow-control feature, which is why I tacked it onto this section. However, you will use this facility in many places, such as triggers, stored procedures, transaction processing, and so on.

The syntax for RAISERROR is as follows:

 RAISERROR ( { msg_id | msg_str } { , severity , state} [ , argument [ ,...n ] ] ) [ WITH option [ ,...n ] ]

However, the simplest syntax to observe at this point for client information is simply RAISERROR(Message, Severity, State). Thus the syntax for a simple message to the user would be RAISERROR(‘This is a non-severe error’, 1,1). More about RAISERROR later.

TRY…CATCH

The new Try…Catch support in T_SQL now implements a error handling process for Transact-SQL similar to the exception handling in .NET Framework (such as in Visual Basic, C#, and C++). You can enclose your Transact-SQL statements in a TRY block and if an error occurs within the TRY block, control is passed to another group of statements enclosed in the CATCH block. The following syntax is the standard TRY…CATCH construct for T-SQL:

 BEGIN TRY     { sql_statement | statement_block } END TRY BEGIN CATCH     { sql_statement | statement_block } END CATCH [ ; ]

 Here is an example: BEGIN TRY   -- Test the impossible divide-by-zero to force an exception.      SELECT 1/0; END TRY BEGIN CATCH     EXECUTE MyErrorHandler END CATCH;