{"id":830,"date":"2018-10-03T14:17:40","date_gmt":"2018-10-03T07:17:40","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=830"},"modified":"2020-07-13T07:29:49","modified_gmt":"2020-07-13T00:29:49","slug":"sql-server-if-else","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-if-else\/","title":{"rendered":"SQL Server IF ELSE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn SQL Server IF...ELSE<\/code> statement to control the flow of program.<\/p>\n\n\n\n

The IF...ELSE<\/code> statement is a control-flow statement that allows you to execute or skip a statement block<\/a> based on a specified condition.<\/p>\n\n\n\n

The IF statement #<\/a><\/h2>\n\n\n\n

The following illustrates the syntax of the IF<\/code> statement:<\/p>\n\n\n

IF boolean_expression   \nBEGIN<\/span>\n    { statement_block }\nEND<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that if the Boolean expression contains a SELECT<\/code> statement, you must enclose the SELECT<\/code> statement in parentheses.<\/p>\n\n\n\n
The following example first gets the sales amount from the sales.order_items<\/code> table in the sample database<\/a> and then prints out a message if the sales amount is greater than 1 million.<\/p>\n\n\n
BEGIN<\/span>\n    DECLARE<\/span> @sales INT<\/span>;\n\n    SELECT<\/span> \n        @sales = SUM<\/span>(list_price * quantity)\n    FROM<\/span>\n        sales.order_items i\n        INNER<\/span> JOIN<\/span> sales.orders o ON<\/span> o.order_id = i.order_id\n    WHERE<\/span>\n        YEAR<\/span>(order_date) = 2018<\/span>;\n\n    SELECT<\/span> @sales;\n\n    IF @sales > 1000000\n    BEGIN<\/span>\n        PRINT 'Great! The sales amount in 2018 is greater than 1,000,000'<\/span>;\n    END<\/span>\nEND<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output of the code block is:<\/p>\n\n\n
Great! The sales amount in 2018 is greater than 1,000,000\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that you have to click the Messages<\/strong> tab to see the above output message:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n\n\n\n
The IF ELSE statement #<\/a><\/h2>\n\n\n\n
When the condition in the IF<\/code> clause evaluates to FALSE<\/code> and you want to execute another statement block, you can use the ELSE<\/code> clause.<\/p>\n\n\n\n
The following illustrates the IF ELSE<\/code> statement:<\/p>\n\n\n
IF Boolean_expression\nBEGIN<\/span>\n    -- Statement block executes when the Boolean expression is TRUE<\/span>\nEND<\/span>\nELSE<\/span>\nBEGIN<\/span>\n    -- Statement block executes when the Boolean expression is FALSE<\/span>\nEND<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Each IF<\/code> statement has a condition. If the condition evaluates to TRUE<\/code> then the statement block in the IF<\/code> clause is executed. If the condition is FALSE<\/code>, then the code block in the ELSE<\/code> clause is executed.<\/p>\n\n\n\n
See the following example:<\/p>\n\n\n
BEGIN<\/span>\n    DECLARE<\/span> @sales INT<\/span>;\n\n    SELECT<\/span> \n        @sales = SUM<\/span>(list_price * quantity)\n    FROM<\/span>\n        sales.order_items i\n        INNER<\/span> JOIN<\/span> sales.orders o ON<\/span> o.order_id = i.order_id\n    WHERE<\/span>\n        YEAR<\/span>(order_date) = 2017<\/span>;\n\n    SELECT<\/span> @sales;\n\n    IF @sales > 10000000\n    BEGIN<\/span>\n        PRINT 'Great! The sales amount in 2018 is greater than 10,000,000'<\/span>;\n    END<\/span>\n    ELSE<\/span>\n    BEGIN<\/span>\n        PRINT 'Sales amount in 2017 did not reach 10,000,000'<\/span>;\n    END<\/span>\nEND<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example:<\/p>\n\n\n\n
First, the following statement sets the total sales in 2017 to the @sales<\/code> variable:<\/p>\n\n\n
    SELECT<\/span> \n        @sales = SUM<\/span>(list_price * quantity)\n    FROM<\/span>\n        sales.order_items i\n        INNER<\/span> JOIN<\/span> sales.orders o ON<\/span> o.order_id = i.order_id\n    WHERE<\/span>\n        YEAR<\/span>(order_date) = 2017<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, this statement returns the sales to the output:<\/p>\n\n\n
    SELECT<\/span> @sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, the IF<\/code> clause checks if the sales amount in 2017 is greater than 10 million. Because the sales amount is less than that, the statement block in the ELSE<\/code> clause executes.<\/p>\n\n\n
    IF @sales > 10000000\n    BEGIN<\/span>\n        PRINT 'Great! The sales amount in 2018 is greater than 10,000,000'<\/span>;\n    END<\/span>\n    ELSE<\/span>\n    BEGIN<\/span>\n        PRINT 'Sales amount in 2017 did not reach 10,000,000'<\/span>;\n    END<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following shows the output:<\/p>\n\n\n
Sales amount did not reach 10,000,000\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Nested IF...ELSE #<\/a><\/h3>\n\n\n\n
SQL Server allows you to nest an IF...ELSE<\/code> statement within inside another IF...ELSE<\/code> statement, see the following example:<\/p>\n\n\n
BEGIN<\/span>\n    DECLARE<\/span> @x INT<\/span> = 10<\/span>,\n            @y INT<\/span> = 20<\/span>;\n\n    IF (@x > 0)\n    BEGIN<\/span>\n        IF<\/span> (@x < @y)\n            PRINT 'x > 0 and x < y'<\/span>;\n        ELSE\n            PRINT 'x > 0 and x >= y';\n    END<\/span>\t\t\t\nEND<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example:<\/p>\n\n\n\n
First, declare two variables @x and @y and set their values to 10 and 20 respectively:<\/p>\n\n\n
DECLARE<\/span> @x INT<\/span> = 10<\/span>,\n        @y INT<\/span> = 20<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, the output IF<\/code> statement check if @x<\/code> is greater than zero. Because @x<\/code> is set to 10, the condition (@x > 10<\/code>) is true. Therefore, the nested IF<\/code> statement executes.<\/p>\n\n\n\n
Finally, the nested IF<\/code> statement check if @x is less than @y ( @x < @y<\/code>). Because @y<\/code> is set to 20,  the condition (@x < @y<\/code>) evaluates to true. The PRINT 'x > 0 and x < y';<\/code> statement in the IF<\/code> branch executes.<\/p>\n\n\n\n
Here is the output:<\/p>\n\n\n
x > 0 and x < y<\/code><\/span><\/pre>\n\n\n
It is a good practice to not nest an IF<\/code> statement inside another statement because it makes the code difficult to read and hard to maintain.<\/p>\n\n\n\n
In this tutorial, you have learned how to use the SQL Server IF...ELSE<\/code> statement to control the flow of code execution.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
In this syntax, if the Boolean_expression<\/code> evaluates to TRUE<\/code> then the statement_block<\/code> in the BEGIN...END<\/a><\/code> block is executed. Otherwise, the statement_block<\/code> is skipped and the control of the program is passed to the statement after the END<\/code> keyword.<\/p>\n\n\n\n