The keyword if, with or without its companion else, introduces a condition that determines whether the next statement is executed. The Transact-SQL statement executes if the condition is satisfied, that is, if it returns TRUE.
The else keyword introduces an alternate Transact-SQL statement that executes when the if condition returns FALSE.
The syntax for if and else is:
if boolean_expression statement [else [if boolean_expression] statement ]
A Boolean expression returns TRUE or FALSE. It can include a column name, a constant, any combination of column names and constants connected by arithmetic or bitwise operators, or a subquery, as long as the subquery returns a single value. If the Boolean expression contains a select statement, the select statement must be enclosed in parentheses, and it must return a single value.
Here is an example of using if alone:
if exists (select postalcode from authors where postalcode = "94705") print "Berkeley author"
If one or more of the postal codes in the authors table has the value “94705,” the message “Berkeley author” prints. The select statement in this example returns a single value, either TRUE or FALSE, because it is used with the keyword exists. The exists keyword functions here just as it does in subqueries. See Chapter 5, “Subqueries: Using Queries Within Other Queries.”
This example uses both if and else, and tests for the presence of user-created objects that have ID numbers greater than 50. If user objects exist, the else clause selects their names, types, and ID numbers.
if (select max(id) from sysobjects) < 50 print "There are no user-created objects in this database." else select name, type, id from sysobjects where id > 50 and type = "U"
(0 rows affected) name type id ------------ ---- --------- authors U 16003088 publishers U 48003202 roysched U 80003316 sales U 112003430 salesdetail U 144003544 titleauthor U 176003658 titles U 208003772 stores U 240003886 discounts U 272004000 au_pix U 304004114 blurbs U 336004228 friends_etc U 704005539 test U 912006280 hello U 1056006793 (14 rows affected)
if...else constructs are frequently used in stored procedures where they test for the existence of some parameter.
if tests can nest within other if tests, either within another if or following an else. The expression in the if test can return only one value. Also, for each if...else construct, there can be one select statement for the if and one for the else. To include more than one select statement, use the begin...end keywords. The maximum number of if tests you can nest varies, depending on the complexity of the select statements (or other language constructs) you include with each if...else construct.
