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
Comments