{"id":149,"date":"2019-09-24T07:53:34","date_gmt":"2019-09-24T07:53:34","guid":{"rendered":"http:\/\/sumitjangid.com\/?p=149"},"modified":"2019-09-24T08:03:46","modified_gmt":"2019-09-24T08:03:46","slug":"if-else","status":"publish","type":"post","link":"http:\/\/sumitjangid.com\/index.php\/2019\/09\/24\/if-else\/","title":{"rendered":"IF Else"},"content":{"rendered":"\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

 IF<\/code> statement<\/h2>\n\n\n\n

The following illustrates the syntax of the IF<\/code> statement:<\/p>\n\n\n\n
1234<\/td>IF boolean_expression   BEGIN    { statement_block }END<\/td><\/tr><\/tbody><\/table>\n\n\n\n

In this syntax, if the Boolean_expression<\/code> evaluates to TRUE<\/code> then the statement_block<\/code> in the BEGIN...END<\/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

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>. Then, it prints out a message if the sales amount is greater than 1 million.<\/p>\n\n\n\n
123456789101112131415161718<\/td>BEGIN    DECLARE @sales INT;     SELECT         @sales = SUM(list_price * quantity)    FROM        sales.order_items i        INNER JOIN sales.orders o ON o.order_id = i.order_id    WHERE        YEAR(order_date) = 2018;     SELECT @sales;     IF @sales > 1000000    BEGIN        PRINT ‘Great! The sales amount in 2018 is greater than 1,000,000’;    ENDEND<\/td><\/tr><\/tbody><\/table>\n\n\n\n

The output of the code block is:<\/p>\n\n\n\n
1<\/td>Great! The sales amount in 2018 is greater than 1,000,000<\/td><\/tr><\/tbody><\/table>\n\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

 IF ELSE<\/code> statement<\/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\n
12345678<\/td>IF Boolean_expressionBEGIN    — Statement block executes when the Boolean expression is TRUE<\/em>ENDELSEBEGIN    — Statement block executes when the Boolean expression is FALSE<\/em>END<\/td><\/tr><\/tbody><\/table>\n\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\n
12345678910111213141516171819202122<\/td>BEGIN    DECLARE @sales INT;     SELECT         @sales = SUM(list_price * quantity)    FROM        sales.order_items i        INNER JOIN sales.orders o ON o.order_id = i.order_id    WHERE        YEAR(order_date) = 2017;     SELECT @sales;     IF @sales > 10000000    BEGIN        PRINT ‘Great! The sales amount in 2018 is greater than 10,000,000’;    END    ELSE    BEGIN        PRINT ‘Sales amount in 2017 did not reach 10,000,000’;    ENDEND<\/td><\/tr><\/tbody><\/table>\n\n\n\n

In this example,<\/p>\n\n\n\n

First, the following statement set the total sales in 2017 to the @sales<\/code> variable:<\/p>\n\n\n\n
1234567<\/td>    SELECT         @sales = SUM(list_price * quantity)    FROM        sales.order_items i        INNER JOIN sales.orders o ON o.order_id = i.order_id    WHERE        YEAR(order_date) = 2017;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Second, this statement returns the sales to the output:<\/p>\n\n\n\n
1<\/td>    SELECT @sales;<\/td><\/tr><\/tbody><\/table>\n\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\n
12345678<\/td>    IF @sales > 10000000    BEGIN        PRINT ‘Great! The sales amount in 2018 is greater than 10,000,000’;    END    ELSE    BEGIN        PRINT ‘Sales amount in 2017 did not reach 10,000,000’;    END<\/td><\/tr><\/tbody><\/table>\n\n\n\n

The following shows the output:<\/p>\n\n\n\n
1<\/td>Sales amount did not reach 10,000,000<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Nested IF...ELSE<\/code><\/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\n
123456789101112<\/td>BEGIN    DECLARE @x INT = 10,            @y INT = 20;     IF (@x > 0)    BEGIN        IF (@x < @y)            PRINT ‘x > 0 and x < y’;        ELSE            PRINT ‘x > 0 and x >= y’;    END END<\/td><\/tr><\/tbody><\/table>\n\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\n
12<\/td>DECLARE @x INT = 10,        @y INT = 20;<\/td><\/tr><\/tbody><\/table>\n\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\n
1<\/td>x > 0 and x < y<\/td><\/tr><\/tbody><\/table>\n\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","protected":false},"excerpt":{"rendered":"

The IF…ELSE statement is a control-flow statement that allows you to execute or skip a statement block based on a specified condition.  IF statement The following illustrates the syntax of the IF statement: 1234 IF boolean_expression   BEGIN    { statement_block }END In this syntax, if… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2,18,19],"tags":[],"_links":{"self":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/149"}],"collection":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/comments?post=149"}],"version-history":[{"count":1,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/149\/revisions"}],"predecessor-version":[{"id":150,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/posts\/149\/revisions\/150"}],"wp:attachment":[{"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/media?parent=149"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/categories?post=149"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sumitjangid.com\/index.php\/wp-json\/wp\/v2\/tags?post=149"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}