Programming Keywords
3 4
Several helpful programming-construct keywords can be used with T-SQL statements to control program flow. These constructs can be used within batches (a group of T-SQL statements that are executed at one time), stored procedures, scripts, and ad hoc queries. (The examples in this section use the pubs database.)
IF…ELSE
The IF…ELSE construct is used to impose conditions that determine which T-SQL statements will execute. The syntax for IF…ELSE is shown here:
IF Boolean_expression T-SQL_statement | block_of_statements [ELSE T-SQL_statement | block_of_statements ]
A Boolean expression is one that returns either TRUE or FALSE. If the expression in the IF clause returns TRUE, the subsequent statements are executed, but the ELSE clause and its statements are not executed. If the expression returns FALSE, only the statements after the ELSE keyword are executed. The variable block_of_statements simply refers to more than one T-SQL statement. When a block of statements is used, you must provide the keywords BEGIN and END to specify the beginning and the end of each block, whether the block is in the IF clause, the ELSE clause, or both.
You can have an IF clause without an ELSE clause. Let's look first at an example that uses IF alone. The following code will test an expression, and if the expression returns TRUE, the subsequent PRINT statement will be executed:
IF (SELECT ytd_sales FROM titles WHERE title_id = 'PC1035') > 5000 PRINT 'Year-to-date sales are greater than $5,000 for PC1035.' GO
The IF expression will evaluate to TRUE because the ytd_sales value for the row with title_id = "PC1035" is 8780. The PRINT statement will be executed, and "Year-to-date sales are greater than $5,000 for PC1035" will be printed to the screen.
Now let's add an ELSE clause to the preceding example and change the > 5000 to > 9000. The code is shown here:
IF (SELECT ytd_sales FROM titles WHERE title_id = 'PC1035') > 9000 PRINT 'Year-to-date sales are greater than $9,000 for PC1035.' ELSE PRINT 'Year-to-date sales are less than or equal to $9,000 for PC1035.' GO
In this case, the PRINT statement after the ELSE clause will be executed because the IF expression returns FALSE.
Let's take this example a step further and add a block of statements after the IF clause and a block after the ELSE clause. Which message will be printed and which query will then be performed will depend on whether the IF condition is TRUE or FALSE. Here is the code:
IF (SELECT ytd_sales FROM titles WHERE title_id = 'PC1035') > 9000 BEGIN PRINT 'Year-to-date sales are greater than $9,000 for PC1035.' SELECT ytd_sales FROM titles WHERE title_id = 'PC1035' END ELSE --ytd_sales must be <= 9000. BEGIN PRINT 'Year-to-date sales are less than or equal to $9,000 for PC1035.' SELECT price FROM titles WHERE title_id = 'PC1035' END GO
The IF expression evaluates to FALSE, so the statements between BEGIN and END in the ELSE clause are executed. The PRINT statement is executed first, and then the SELECT statement is executed, showing that the book costs $22.95.
You can also use nested IF statements after an IF clause or after an ELSE clause. For example, to use nested IF…ELSE statements to determine what range the average ytd_sales value for all titles falls into, run the following code:
IF (SELECT avg(ytd_sales) FROM titles) < 10000 IF (SELECT avg(ytd_sales) FROM titles) < 5000 IF (SELECT avg(ytd_sales) FROM titles) < 2000 PRINT 'Average year-to-date sales are less than $2,000.' ELSE PRINT 'Average year-to-date sales are between $2,000 and $4,999.' ELSE PRINT 'Average year-to-date sales are between $5,000 and $9,999.' ELSE PRINT 'Average year-to-date sales are greater than $9,999.' GO
When you run this code, you will see the following warning message twice in the output: "Warning: Null value eliminated from aggregate." This message means simply that the null values that exist in the ytd_sales column were not counted as values when the average was calculated. The final result from this code will be "Average year-to-date sales are between $5,000 and $9,999" because the average is $6,090. Be careful when using nested IF statements. It's easy to mix up which IF belongs with which ELSE, or to leave an IF without a corresponding ELSE. Using tabs for spacing, as in the preceding query, makes it easier to see which IF…ELSE pairs belong together.
WHILE
The WHILE construct is used to test a condition that causes the repeated execution of a statement or block of statements while the condition is TRUE. This is commonly known as a WHILE loop because the code inside the WHILE construct is repeated in loop fashion. The syntax is shown here:
WHILE Boolean_expression SQL_statement | block_of_statements [BREAK] SQL_statement | block_of_statements [CONTINUE]
As in IF…ELSE clauses, you specify a block of statements in a WHILE loop by using BEGIN and END. The BREAK keyword causes an exit from the WHILE loop, and execution will continue with any statements after the end of the WHILE loop. If the WHILE loop is nested within other WHILE loops, the BREAK keyword causes an exit from only the WHILE loop in which it is contained; any statements outside the loop, as well as the outer loops, will continue to execute. The CONTINUE keyword in a loop specifies that the statements between the BEGIN and END keywords for that WHILE loop should restart, ignoring any other statements after CONTINUE.
Let's look at an example that uses a simple WHILE loop to repeatedly execute one UPDATE statement. The condition in this WHILE loop tests whether the average value of the royalty column is less than 20. If the test returns TRUE, the royalty column is updated by increasing all royalties by 5 percent. The WHILE condition is then checked again, and the update is repeated until the average value of the royalty column is 20 or higher. The code is as follows:
WHILE (SELECT AVG(royalty) FROM roysched) < 20 UPDATE roysched SET royalty = royalty * 1.05 GO
Because the average value of the royalty column was originally 15, this WHILE loop is executed 21 times before the average value reaches 20; the loop then terminates because the test condition returns FALSE.
Now let's look at an example that uses BEGIN, BREAK, CONTINUE, and END in a WHILE loop. We will loop through the UPDATE statement until the average royalty exceeds 25 percent. If, however, during the loop, the maximum royalty in the table exceeds 27 percent, we will break out of the loop no matter what the average is. We'll also add a SELECT statement after the end of the WHILE loop. Here is the T-SQL code:
WHILE (SELECT AVG(royalty) FROM roysched) < 25 BEGIN UPDATE roysched SET royalty = royalty * 1.05 IF (SELECT MAX(royalty)FROM roysched) > 27 BREAK ELSE CONTINUE END SELECT MAX(royalty) AS "MAX royalty" FROM roysched GO
This loop will execute only one time because a royalty value greater than 27 already exists in the table. The UPDATE statement is executed once because the average royalty is less than 25 percent. Then the IF statement is tested and returns TRUE, so BREAK is executed, causing an exit from the WHILE loop. Program execution then continues with the statement following the END keyword, the last SELECT statement.
Remember that you can also use nested WHILE loops, but keep in mind that a BREAK or CONTINUE keyword applies only to the loop from which it was called, not to outer WHILE loops.
CASE
The CASE keyword is used to evaluate a list of conditions and return one of multiple possible results. Which result is returned will depend on which condition is equal to another specified condition or is true. Common uses for CASE are to replace a code or an abbreviated value with a more readable value, and to categorize values, as will be shown in our examples in this section. The two formats for the CASE construct are called simple and searched. The simple format specifies an input expression value after CASE that will be tested for equality with the value in the WHEN expression or expressions. The searched format tests a Boolean expression for TRUE or FALSE rather than testing for equality with a value. Let's first look at the simple format. The syntax for a simple-format CASE clause is shown here:
CASE input_expression WHEN when_expression THEN result_expression [WHEN when_expression THEN result_expression…n] [ELSE else_result_expression ] END
The result expression value will be returned if the corresponding WHEN expression value is equal to the input expression value. The expressions are compared in the order in which they are listed in the CASE clause. If no match is made, the ELSE result expression value will be returned if one is specified, or NULL is returned if an ELSE result expression value is not specified. Note that in the simple-format CASE clause, the input expression value and the WHEN expression value must have the same data type or allow implicit conversion.
This example uses a simple-format CASE clause within a SELECT statement. The payterms column of the sales table contains one of these values for each row: Net 30, Net 60, On invoice, or None. This T-SQL statement allows an alternative (easier to understand) value to be displayed in the payterms column:
SELECT 'Payment Terms' = CASE payterms WHEN 'Net 30' THEN 'Payable 30 days after invoice' WHEN 'Net 60' THEN 'Payable 60 days after invoice' WHEN 'On invoice' THEN 'Payable upon receipt of invoice' ELSE 'None' END, title_id FROM sales ORDER BY payterms GO
This CASE clause tests the value of payterms for each row specified in the SELECT statement. The result expression value is returned when the WHEN expression value equals the value in payterms. The results from the CASE clause appear in the Payment Terms column in the result set, as shown here:
Payment Terms title_id ----------------------------------------- -------- Payable 30 days after invoice PC8888 Payable 30 days after invoice TC3218 Payable 30 days after invoice TC4203 Payable 30 days after invoice TC7777 Payable 30 days after invoice PS2091 Payable 30 days after invoice MC3021 Payable 30 days after invoice BU1111 Payable 30 days after invoice PC1035 Payable 60 days after invoice PS1372 Payable 60 days after invoice PS2106 Payable 60 days after invoice PS3333 Payable 60 days after invoice PS7777 Payable 60 days after invoice BU7832 Payable 60 days after invoice MC2222 Payable 60 days after invoice PS2091 Payable 60 days after invoice BU1032 Payable 60 days after invoice PS2091 Payable upon receipt of invoice PS2091 Payable upon receipt of invoice BU1032 Payable upon receipt of invoice BU2075 Payable upon receipt of invoice MC3021 (21 row(s) affected)
Now let's look at the second CASE clause format, the searched format. The syntax for the searched-format CASE clause is shown here:
CASE WHEN Boolean_expression THEN result_expression [WHEN Boolean_expression THEN result_expression…n] [ELSE else_result_expression ] END
The difference between the simple-format CASE clause and the searched-format CASE clause is that the searched-format CASE clause does not have an input expression value after the CASE keyword; it has Boolean expressions after the WHEN keywords to test for TRUE or FALSE rather than for equality. A searched-format CASE clause tests the Boolean expression values and displays the corresponding result expression value for the first Boolean expression value that returns TRUE. (The expressions are tested in the order in which they are listed.)
For example, the CASE clause within the following SELECT statement will test each row for its price value and will return a character string based on the price range in which the price of the book falls:
SELECT 'Price Range' = CASE WHEN price BETWEEN .01 AND 10.00 THEN 'Inexpensive: $10.00 or less' WHEN price BETWEEN 10.01 AND 20.00 THEN 'Moderate: $10.01 to $20.00' WHEN price BETWEEN 20.01 AND 30.00 THEN 'Semi-expensive: $20.01 to $30.00' WHEN price BETWEEN 30.01 AND 50.00 THEN 'Expensive: $30.01 to $50.00' WHEN price IS NULL THEN 'No price listed' ELSE 'Very expensive!' END, title_id FROM titles ORDER BY price GO
The result set is shown here:
Price Range title_id -------------------------------- -------- No price listed MC3026 No price listed PC9999 Inexpensive: $10.00 or less MC3021 Inexpensive: $10.00 or less BU2075 Inexpensive: $10.00 or less PS2106 Inexpensive: $10.00 or less PS7777 Moderate: $10.01 to $20.00 PS2091 Moderate: $10.01 to $20.00 BU1111 Moderate: $10.01 to $20.00 TC4203 Moderate: $10.01 to $20.00 TC7777 Moderate: $10.01 to $20.00 BU1032 Moderate: $10.01 to $20.00 BU7832 Moderate: $10.01 to $20.00 MC2222 Moderate: $10.01 to $20.00 PS3333 Moderate: $10.01 to $20.00 PC8888 Semi-expensive: $20.01 to $30.00 TC3218 Semi-expensive: $20.01 to $30.00 PS1372 Semi-expensive: $20.01 to $30.00 PC1035 (18 row(s) affected)
NOTE
In these two CASE clause examples, we inserted a comma after the END keyword because the entire CASE clause was used as part of column_list in the SELECT clause along with title_id. In other words, the entire CASE clause was simply an entry in column_list. This is the most common use of the CASE keyword.
Other Keywords
The additional T-SQL keywords available for controlling programming flow are listed here:
- GOTO label Directs processing to continue at label as defined in GOTO
- RETURN Exits unconditionally from a query or procedure
- WAITFOR Sets a delay or a specific time for a statement to execute
MORE INFO
For details about how to use these keywords, check the Books Online index for "GOTO," "RETURN," and "WAITFOR" and examine the topics listed in the Topics Found dialog box.
EAN: N/A
Pages: 264