1. Loops and Conditions

The Loop and condition are useful when writing the SQL Scripts to achieve the desired goals. In this SQL Server example, we will learn:

1. If & Else Conditions
2. Case & When Structures
3. While loops

2. If..Else Condition

The If & Else statement decides which execution path to follow in the SQL Script. If statement will come with a condition and based on the condition output, the execution path will be followed. Now, look at the example below:

Marker Based Explanation

1. A Variable @Price stores the initial value as ‘Low’. The variable is declared as varchar type.
2. Here, we use the if condition which tests the content of the variable @Price with the constant ‘High’. Note, the usage of the comparison operator = and like this one can use other comparison operators as well. The conditions can be combine using the OR and AND logical operators.
3. When the condition evaluates to true, the block of statement immediately after the If condition will be executed. It is a good practice to include the code block within Begin and End pair. The Begin and End pair helps in combining more than one scripting statement. If we want to execute two or more statement when condition evaluates to true, then those must be within the Begin and End pair.
4. The Else statement tells which code block to follow when condition evaluates to false. In our case, the condition outcome is false.
5. This is the code block for the Else part which lists titles with amount lesser than 18.

The result of executing the script is shown below:

Marker Based Explanation

1. The variable is holding the value as ‘LOW’. When executing the script, the flow goes to else block and fires the query to pull all the titles with price amount below 18.
2. The query output shows the price value of the title.
3. Here, we changed the variable value to ‘HIGH’. The query flow goes to the if block now.
4. Result shows that book titles with price amount more than 18$.

3. Case When Then Structure

If we want to test multiple vales and decide the outcome, we can use the Case..When..Then structure. In SQL server, this is useful when we want to transform one set of values into other. Now look at the example below. Here, we tested the Price column as Case Expression and based on the outcome we decide price is Low or Medium or High.

Marker Based Explanation

1. In the select list, we added a CASE structure for the fifth Colum.
2. Case can be combined with one or more WHEN clauses to form the case expressions. In our example, we formed three such case conditions via When clause.
3. If the case expression in WHEN clause evaluates to true, the execution lands into THEN clause and exits the case structure. In our example, we use the THEN clause to decide the price category as Low, or Normal or High.
4. One can use the ELSE clause when there is no match in case expression. In our case, we display N/A applicable when the Price column is null or not holding a valid value.
5. We named this entire case structure output as Price Rating column alias.
6. Result shows a new derived column from our case structure and displays the price category.

4. While Loop in TSQL Script

To perform iteration on the SQL Script, one can use the While Loop. It expects a condition and executes the body till condition’s outcome is false. Have a look at the below code:

Maker Based Explanation

1. First, we declared a variable in the script. We named this variable as @LoopVar and initialized it to hold the value 1.
2. Here you can see a While loop evaluating the condition stating variable must be less than 11. This condition’s outcome is false when the @LoopVar holds value higher than 11.
3. Inside the body of the loop, we increment the @LoopVar by one. This will make the execution quit the WHILE loop after running the body for 10 times.
4. The output shows value in the @LoopVar was printed starting from 1 to 10. This also proves that loop body executed 10 times repeatedly.

5. Code Reference

5.1

5.2

6. Summary

The conditional and loop construct which you learned here will help in writing Stored procedure and functions in an effective way. When you are forming the loop, make sure the loop will exit properly by examining the condition and variables involved in it.

• Strong Name Tool – Private & Public
• Stateless Session Bean – Code Snippets #01

Categories: SQL Server

Tags: Case...When...Then, If...Else, While Loop

This site uses Akismet to reduce spam. Learn how your comment data is processed.