SQL Server allows you to use programming constructs in the batches for conditional execution of statements. For example, you need to retrieve data based on a condition. If the condition is not satisfied, a message should be displayed.
The SQL Server allows you to use the following constructs to control the flow of statements:
Using the IF….ELSE Statement
You can use the IF…..ELSE statement for conditional execution of SQL statements. A particular action is performed when the given condition on evaluates to TRUE and another action is performed when the given condition evaluates to FALSE.
The syntax of IF….ELSE statement is:
IF Boolean_expression
{sql_statement | statement_block}
ELSE
{sql_statement | statement_block}]
Where,
The following example retrieves the pay rate of an employee from the EmployeePayHistory table to a variable, @Rate. The value of the @Rate variable is compared with the value 15 by using the <(less than) comparison operator. Based on the condition, different messages are displayed.
DECLARE @Rate money
SELECT @Rate = Rate FROM HumanResources.EmployeeHistory
WHERE EmployeeID = 23
IF @Rate < 15
PRINT ‘Review of the rate is required’
ELSE
BEGIN
PRINT ‘Review of the rate is not required’
PRINT ‘Rate =’
PRINT @Rate
END
GO
In the preceding example, the IF statement checks if the rate variable is storing a value less than 15. If the result is true, the PRINT statement displays “Review of the rate is required” else it displays “Review of the rate is not required”. Further, the next PRN statement displays the value of the rate.
Consider another example, where a check is performed to see the existence of the sales department. If the Sales department exists, all the details are displayed otherwise, a user-defined message is displayed.
IF EXISTS (SELECT * FROM HumanResources.Department WHERE Name = ‘Sales’)
BEGIN
SELECT * FROM HumanResources.Department WHERE Name = ‘Sales’
END
ELSE
PRINT ‘Department details not available’
GO
The SQL Server allows you to use the following constructs to control the flow of statements:
- IF…..ELSE statement
- CASE statement
- WHILE statement
Using the IF….ELSE Statement
You can use the IF…..ELSE statement for conditional execution of SQL statements. A particular action is performed when the given condition on evaluates to TRUE and another action is performed when the given condition evaluates to FALSE.
The syntax of IF….ELSE statement is:
IF Boolean_expression
{sql_statement | statement_block}
ELSE
{sql_statement | statement_block}]
Where,
- Boolean_expression specifies the condition that evaluates to either TRUE or FALSE. Sql_statement specifies a T-SQL statement.
- Statement_block is a collection of T-SQL statements.
The following example retrieves the pay rate of an employee from the EmployeePayHistory table to a variable, @Rate. The value of the @Rate variable is compared with the value 15 by using the <(less than) comparison operator. Based on the condition, different messages are displayed.
DECLARE @Rate money
SELECT @Rate = Rate FROM HumanResources.EmployeeHistory
WHERE EmployeeID = 23
IF @Rate < 15
PRINT ‘Review of the rate is required’
ELSE
BEGIN
PRINT ‘Review of the rate is not required’
PRINT ‘Rate =’
PRINT @Rate
END
GO
In the preceding example, the IF statement checks if the rate variable is storing a value less than 15. If the result is true, the PRINT statement displays “Review of the rate is required” else it displays “Review of the rate is not required”. Further, the next PRN statement displays the value of the rate.
Consider another example, where a check is performed to see the existence of the sales department. If the Sales department exists, all the details are displayed otherwise, a user-defined message is displayed.
IF EXISTS (SELECT * FROM HumanResources.Department WHERE Name = ‘Sales’)
BEGIN
SELECT * FROM HumanResources.Department WHERE Name = ‘Sales’
END
ELSE
PRINT ‘Department details not available’
GO