TSQL Tutorial 9: Control Structures(While, If and Case)

I want to quickly go over the 3 main control structures in TSQL in this tutorial. Control structures are structures that help us control how we want our program to run based on different situations.

Let’s take a look at the Structures one at a time.

The IF: This structure controls which path to take based on a condition. The execution point never goes back after passing through the location of the IF structure.

This is the syntax of the IF structure in TSQL

IF <condition>

BEGIN

<Perform this actions>

END

ELSE IF <another condition>

BEGIN

<Perform this other action>

END

ELSE

BEGIN

<Perform this other action if none of the above conditions met>

END

Lets say you have a column in a table that assign the Seniority level of employees. Lets say employees who work for more than 10 years and above have seniority level 3, those who work from 5 to 9 years have seniority level 2 and those who work less than 5 years have seniority level 1. Now, assume there is another column in the table that tells the hire date of all employees. So to assign a seniority level to a particular employee, you can simply use the IF structure to automatically assign the level, lets do that using TSQL.

DECLARE @level INT

IF (DATEDIFF(yy, hiredate, GETDATE()) < 5)

SET @level = 1

ELSE IF(DATEDIFF(yy, hiredate, GETDATE()) BETWEEN 5 AND 9)

SET @level = 2

ELSE

SET @level = 3

Now you can use the @level to update that seniority level. That’s how good and helpful control structures can help us to accomplish tasks easily.

Let’s look at the while control structure. This control structure is used for loops. A loop is simply something that has to execute more than once if a certain condition is met. The structure of the while loop structure looks like this,

WHILE <condition>

BEGIN

<perform this action>

END

Lets say you want to perform a task multiple of times until a certain condition is met, for example, you want to print all numbers from 1 to 100. If there is no while structure, then you have to write a line of code to write each number, but with the while loop. a single code can be reused in a loop  to accomplish this task. Let’s see how this is done.

DECLARE @counter INT = 1

WHILE(@counter <=100)

BEGIN

PRINT @counter

SET @counter = @counter + 1

END

The next control structure is the CASE. This structure very close to the if structure. Let’s see the syntax

CASE

WHEN <condition> THEN <return this>

WHEN <condition> THEN <return that>

ELSE   <return this defaut>

END

Lets write the same example with the IF structure using CASE

DECLARE @level INT

SET @level =

CASE

WHEN (DATEDIFF(yy, hiredate, GETDATE()) < 5) THEN 1

WHEN (DATEDIFF(yy, hiredate, GETDATE()) BETWEEN 5 AND 9) THEN 2

ELSE 3

END

The advantage of the CASE over the IF structure is that, you can actually give a column alias name to the outcome of the result being retrieve during the case but you can’t do that with the IF structure. Like this,

DECLARE @level INT

SET @level =

CASE

WHEN (DATEDIFF(yy, hiredate, GETDATE()) < 5) THEN 1

WHEN (DATEDIFF(yy, hiredate, GETDATE()) BETWEEN 5 AND 9) THEN 2

ELSE 3

END as ‘Level’

This means, the new column under which this result will show is called Level. But you can’t do that with the IF control structure.

In our next tutorial, l’ll be talking about aggregate functions. Keep in touch, Peace.