There are multiple variations of IF statements:
The simple IF construct is used to evaluate a Boolean condition and execute an appropriate set of commands. For instance the following example determines whether the current day of the week is Friday:
IF DATEPART(dw, GETDATE()) = 6 BEGIN PRINT 'TGI Friday' END
We can easily extend the same construct by adding an ELSE clause to take the alternative course of action and calculate the number of days before Friday:
IF DATEPART(dw, GETDATE()) = 6 BEGIN PRINT 'TGI Friday' END ELSE BEGIN SELECT 'Sorry, Friday is ' + CONVERT(VARCHAR(1), (6-DATEPART(dw, GETDATE())) )+ ' days away' END
Alternatively we can determine the Boolean value of a condition based on the outcome of a SELECT statement:
IF (SELECT COUNT(*) FROM authors) > 20 PRINT 'authors table contains more than 20 records'
Note: If you wish to execute multiple statements depending on the value of a condition, be sure to enclose those statements within a BEGIN END block
ometimes you have to check for multiple conditions and control your program accordingly. You can do so by combining these conditions with AND and OR within the simple IF construct. The following example demonstrates two conditions combined with AND operator:
IF (SELECT COUNT(*) FROM authors) > 20 AND (SELECT COUNT(*) FROM authors WHERE state = 'ca')>0 PRINT 'authors table contains more than 20 records' SELECT CAST((SELECT COUNT(*) FROM authors WHERE state = 'ca') AS VARCHAR(2)) + ' of them are from california'
The previous query will return the number of authors in CA only if the authors table contains any authors from that state and only if the total number of authors is greater than 20. Therefore the AND operator returns true only if both parts of the condition are true.You can also combine conditions with the OR operator, which checks both conditions and returns TRUE if one of them is TRUE or if both of them are true. The OR operator returns FALSE if both parts of the condition are FALSE. Although you can use the OR operator within a simple IF construct it is not recommended to do so. If you have to check for multiple conditions combined with OR logic, it is recommended to use either nested IF statements or CASE operator.
IF EXISTS construct checks for the existence of a record fitting a specified criterion or any records in the specified table and takes action accordingly. This is a great approach to take if you don't have to retrieve any values from a table. Since you cannot do any data retrieval with EXISTS, the SELECT statement within the EXISTS clause does not have to specify any column names - the "*" operator will suffice. In fact IF EXISTS will be more efficient than IF SELECT. The following example checks whether there are any authors from KS in the authors table:
IF EXISTS (SELECT * FROM authors WHERE state = 'ks') PRINT 'found author(s) from KS'
As we noted earlier, if you have to check multiple conditions you can use nested IF ELSE statements. These should be handled with care and appropriately indented to make the code readable. If you've used any other programming language you'll find T-SQL nested IFs very easy to get used to. The following example executes appropriate user stored procedures depending on the price of "abc" stocks:
DECLARE @stock_price SMALLMONEY SELECT @stock_price = stock_price FROM high_yield_stock WHERE stock_symbol = 'abc' IF @stock_price > 10.01 BEGIN EXEC usp_alert_for_high_prices END ELSE IF @stock_price BETWEEN 5.01 and 10.00 BEGIN EXEC usp_notify_managers END ELSE EXEC usp_alert_for_price_drop
If the outer condition evaluates to TRUE, the inner conditions will not be checked. This is especially useful if the evaluation criteria require multi-table joins or searches which are resource intensive.
IF NOT (SELECT COUNT(*) FROM sales WHERE ord_date BETWEEN '1/1/95' AND '12/31/95') > 0 BEGIN PRINT 'no sales have been recorded in 1995' END