SQL IF Statement for Conditional Logic

Problem

I’m learning T-SQL and need to know how to use an IF statement to include conditional statements within a stored procedure, function, trigger, or script in Microsoft SQL Server.

Solution

The IF statement in T-SQL works very much like the IF statement in most modern programming languages. It allows a line of code or a block of code to run only if certain conditions are met. If the conditions are not met, the code is skipped, and execution moves to later lines of code.

SQL IF Statement Basics

The IF statement is very simple to use. It works exactly like the WHERE clause of a SQL statement, only it isn’t attached to a query. The keyword IF is followed by an argument or group of arguments combined with AND or OR keywords. An argument is a logical comparison that evaluates to either true or false. Some examples of an argument might be “@NumberValue < 1”, “@TextValue =’Hello’ ” ,or “BooleanFunction()”.

Combining AND and OR keywords can sometimes yield surprising results. This tip about the WHERE clause details how those keywords work together.

When the argument(s) evaluate as TRUE, the subsequent code is executed. If the argument(s) evaluates as FALSE, then the subsequent line or lines of code are skipped.

Using SQL IF to Control Execution of One Statement

Consider this statement block of code that includes IF statements. Here is the syntax that can be run in SQL Server Management Studio (SSMS):

DECLARE @MSSQLTips INT = 1;
 
IF @MSSQLTips = 0 PRINT 'It is zero';
 
IF @MSSQLTips <> 0 PRINT 'It is not zero';

A variable is declared with a numeric type and set to the value of 1. The first IF statement uses an argument to compare that value to the constant zero. There are no quotes on the 0 because it is numeric. Since it evaluates as false, the text “It is zero” will not be printed. Execution will move on to the next statement, another IF statement. The second IF statement compares the same variable to the same constant value, but this time uses the not equals operator. This will evaluate as true, and the text “It is not zero” will execute.

The formatting, carriage returns/line feeds, and white space don’t matter in T-SQL programming. This means the code will work the same if the potential action is not on the same line as the IF statement. It is a common formatting practice to write IF statements in this style.

This version of the code is functionally equivalent to the one above. Note that the IF line does NOT have a semicolon.

DECLARE @MSSQLTips INT = 1;
 
IF @MSSQLTips = 0 
  PRINT 'It is zero.';
 
IF @MSSQLTips <> 0
  PRINT 'It is not zero.';

The single statement protected by the IF keyword may include multiple lines of actual code as long as it remains a single executable statement. See this syntax where there are several lines of code, but only a single action takes place.

DECLARE @MSSQLTips INT = 1;
 
IF @MSSQLTips = 0
  SELECT 'There is no database zero' AS [name];
 
IF @MSSQLTips <> 0
  SELECT [name]
  FROM sys.databases
  WHERE database_id = @MSSQLTips;

Using SQL IF with BEGIN and END to Control Execution of a Group of Statements

Often, it is more than a single code statement whose execution needs to be protected via the IF keyword. Since the IF statement only protects the next single statement, a block of code must be encapsulated by BEGIN and END keywords to group them together.

DECLARE @MSSQLTips INT = 0;
DECLARE @ReturnValue SMALLINT;
 
IF @MSSQLTips = 0
BEGIN
  SELECT 'There is no database zero' AS [name];
 
  SET @ReturnValue = -1;
END;
 
IF @MSSQLTips <> 0 
BEGIN
  SELECT [name] -- SQL SELECT
  FROM sys.databases
  WHERE database_id = @MSSQLTips;
 
  SET @ReturnValue = 0;
END;

While the BEGIN and END statements are only required for multi-statement conditional scenarios, they can be optionally used on single-statement scenarios. It is this author’s opinion that the BEGIN and END keywords should be used on most, if not all, IF statements. What seems to happen all too often when these keywords are treated as optional is that one author writes a single-statement IF without the BEGIN and END; then later, another developer comes in and adds an additional statement while forgetting to add the — no longer optional — BEGIN and END, and gets unexpected results. This can be avoided if the BEGIN and END are always added — even when they are optional.

SQL ELSEIF and ELSE Condition

Like many other programming languages, T-SQL can pair the IF keyword with ELSE IF and ELSE to more easily check multiple mutually exclusive scenarios. When combined, the first statement must be a single IF statement. Optionally, one or more ELSE IF statements can follow it. Finally, a single, optional ELSE statement can be the last item.

The IF statement will work exactly as above. The ELSE IF differs from the IF statements shown to this point because it will only attempt to execute when the first IF evaluates as false. If the first IF evaluates as true, it will never be considered. Any subsequent ELSE IF blocks will only attempt to execute if every IF or ELSE IF above it has been evaluated as false. Finally, the ELSE statement will only execute when every IF and ELSE IF above it has already been evaluated as false. The ELSE does not have an argument attached to it. In a scenario where the code execution reaches the ELSE statement, it will execute.

Consider this framework of code.

IF @MSSQLTips = 1 --Argument 1
BEGIN --Block 1
 
END
ELSE IF @MSSQLTips = 2  --Argument 2
BEGIN --Block 2
 
END
ELSE  -- In all other cases
BEGIN --Block 3
 
END --Final End

Argument 1 code will execute. If that evaluates as true, then the Block 1 code will execute. Once Block 1 executes, execution will move on to the line Final End. In this scenario, Argument 2 is never attempted. Blocks 2 and 3 codes never execute — even if Argument 2 was true.

If Argument 1 evaluates as false, then Argument 2 is attempted. If Argument 2 evaluates as true, then the code in Block 2 is executed. Afterward, execution is moved to the line labeled Final End. The code in Blocks 1 and 3 are not executed.

If Argument 1 evaluates as false and Argument 2 also evaluates as false, then execution is moved to the ELSE line labeled “In all other cases.” Since there is no argument on this line, the code labeled Block 3 is executed. This block ends on Final End. The code in Blocks 1 and 2 are not executed.

While this example only had one ELSE IF section, there can be an unlimited number of these sections. This example also had an ELSE. It is quite rare to exclude ELSE at the end of a block of code such as this; however, it is technically optional. Exclude it if there is no remaining scenario.

In the first example of this tip, two scenarios were handled with two IF statements. That could be rewritten to include one IF statement with an ELSE for the second.

DECLARE @MSSQLTips INT = 0;
 
IF @MSSQLTips = 0
BEGIN
  SELECT 'There is no database zero' AS [name];
END;
 
ELSE
BEGIN
  PRINT 'It is not zero.';
END;

Using a Boolean Function in an Argument

The most common Boolean function found in a T-SQL IF statement is the EXISTS function. When combined with a query, this function returns true when the query returns at least one row. It returns false when the query returns no rows. Since the function evaluates as true or false, there is no need to use an = or any other operator. The NOT keyword will reverse the function.

While this section and example use the EXISTS function, these facts will hold true for any Boolean function.

The following example SELECT statement checks to see if a customer id exists in the customer table. IF statements combined with the EXISTS function are common to validate parameter values.

IF NOT EXISTS(SELECT * FROM Sales.Customers WHERE CustomerID = 5) PRINT 'Customer 5 is invalid';

Next Steps

Resources

Community

Subscribe