top of page
Marek Vavrovic

Control-of-Flow

Updated: Mar 21, 2020

Flow-control statements from T-SQL are rather rudimentary compared to similar commands in other modern programming languages such as Visual Basic and C#. Their use requires knowledge and some skill to overcome their lack of user friendliness. However, on a positive note, they allow the creation of very complex procedures. These statements let you control the execution flow within a T-SQL batch or stored procedure, and they let you use T-SQL to execute complex programming tasks.


IF...ELSE Statement

syntax:

IF <Boolean expression>

<SQL code to run if TRUE>

ELSE

<SQL code to run if TRUE>


IF <Boolean expression>

BEGIN

<SQL code to run if TRUE>

END

ELSE

BEGIN

<SQL code to run if FALSE>

END


Example 1: IF statement without BEGIN...END block:


DECLARE @site_value INT;

SET @site_value = 15;


IF (@site_value < 25)

PRINT 'Congratulations';

ELSE

PRINT 'CheckYourMath';


GO



Example 2: IF statement with BEGIN…END block.


DECLARE @Age int = 81


IF (@Age <75)

BEGIN

PRINT 'You are bellow 75'

END

ELSE

BEGIN

SELECT @age = CASE

WHEN @Age > 75 and @Age <= 80 THEN 1

ELSE 2

END

IF (@Age = 1)

PRINT 'You are old';

ELSE

PRINT 'You are very old';

PRINT 'Value of the variable @Age is: ' + cast(@Age as varchar(10))

END



Example 3, IF statement: Increasing salary by 10%. Using temporary table to show which rows have been affected by UPDATE statement.


DECLARE @Salary AS INT

SELECT @Salary = Salary from Employee

IF (@Salary < 1000)

BEGIN

DECLARE @UpdatedSalary TABLE (

ID INT,

NAME VARCHAR(10),

SALARY INT)

UPDATE Employee SET Salary *= 1.10

OUTPUT deleted.id, deleted.name, deleted.salary INTO @UpdatedSalary

WHERE Salary < 1000


SELECT * FROM @UpdatedSalary;

SELECT * FROM Employee;

END


WHILE Statement

Sets a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.


Example1: WHILE loop needs BEGIN...END block and the part of the code which is incrementing the initial value. Without these two components you will end up with an infinite loop.


DECLARE @increment INT

SET @increment = 0


WHILE (@increment <= 10)

BEGIN

PRINT 'Hello World ' + CAST(@increment AS CHAR(2))

SET @increment += 1

END



Example 2: In the next example I increase all salaries by 10%. Update statement after While loop will be executing until the sum of all salaries is less than 12000.


DECLARE @TotalSalary int = 0;

Select @TotalSalary = SUM(Salary) FROM Employee


WHILE (@TotalSalary < 12000)

BEGIN

UPDATE Employee SET Salary *= 1.10;

SELECT @TotalSalary = SUM(Salary) FROM Employee

SELECT SUM(Salary) [10%increse] FROM Employee

END



BREAK and CONTINUE

In SQL Server, the BREAK statement is used when you want to exit from a WHILE LOOP and execute the next statements after the loop's END statement.


CONTINUE restarts a WHILE loop. Any statements after the CONTINUE keyword are ignored.


DECLARE @increment INT

SET @increment = 0


WHILE (@increment < 100)

BEGIN

PRINT 'Hello World ' + CAST(@increment AS CHAR(2))

SET @increment = @increment + 1

IF (@increment = 10)

BREAK

ELSE

CONTINUE

PRINT 'THIS IS THE END!'

SELECT * FROM Employee;

END



GOTO label

The GOTO statement causes the code to branch to the label after the GOTO statement.

In the next example, thanks to the second IF statement a and GOTO label the entire code acts like a WHILE statement.


DECLARE @i int

SET @i = 0


do_it_again:

IF @i < 10

BEGIN

PRINT 'Hello World ' + CAST(@i AS CHAR(2))

SET @i = @i + 1

END

IF @i < 10

GOTO do_it_again


This time I want to display a message saying “Error 404” instead of printing number 7



Declare @i int = 0

While (@i<10)

BEGIN

IF (@i = 7)

GOTO msg

Print @i


GoTo Increment

msg:

Print 'error 404'

Increment:

SET @i = @i+1

END





210 views0 comments

Comments


bottom of page