T-SQL Tutorial

T-SQL Control-of-Flow


This article describes how to use the T-SQL Control-of-Flow in SQL Server database. To control the order of execution in SQL Server in sequential order, it has several control of flow keywords. These keywords are known as Control-of-flow language. Following are the 10 main SQL Server Control-of-flow keywords: BEGIN...END, BREAK, CONTINUE, GOTO label, IF...ELSE, RETURN, THROW, TRY...CATCH, WAITFOR, WHILE.


1. BEGIN...END

The control-of-flow BEGIN...END keyword groups Transact SQL statements in a block to execute.

The syntax for BEGIN...END

BEGIN
{ sql_statement | statement_block }
END

Example

USE model
GO
BEGIN TRANSACTION
IF @@TRANCOUNT = 0
BEGIN
INSERT INTO students(id, name, birthday, city)
VALUES (6, 'Tom', '1991-10-07', 'Boston');
SELECT id FROM students WHERE Name = 'Sarah';
END;
ROLLBACK TRANSACTION;
PRINT N'Rolled back the transaction.';
GO


2. BREAK

Use BREAK keyword as an exit statement. If BREAK is inserted into an WHILE loop, once the BREAK statement is executed, it exits the current loop and executes the further statements.

The syntax for BREAK

BREAK

Example

In the following example, we will show how to use the BREAK keyword to exit a WHILE statement. First, declare and set the variable @num = 1. This variable is incremented by the value 1 as long as the WHILE statement is run. To exit the WHILE statement, I set the condition that @num > 2. When running the script, the Example for SQL Server message will be displayed twice.

USE model
GO
DECLARE @num int
SET @num = 1
WHILE @num < 5
BEGIN
PRINT 'Example for SQL Server'
SET @num = @num + 1
IF @num > 2
BREAK
END;
GO


3. CONTINUE

The main functionality of CONTINUE is to restart a WHILE LOOP by ignoring the statements after the CONTINUE keyword.

The syntax for CONTINUE

CONTINUE

Example

The example below shows how to use the CONTINUE keyword to continue running a WHILE statement. The WHILE statement is set to run from 0 to 4, with the help of the @num variable that is incremented by 1 as long as the conditions in the WHILE block are met. If the variable @num will have the value 3, then exit WHILE using BREAK, otherwise, using the CONTINUE keyword, WHILE will continue running. At the end of WHILE's run, the message Example of T-SQL loops will be displayed three times.

USE model
GO
DECLARE @num int
SET @num = 0
WHILE @num < 5
BEGIN
PRINT 'Example of T-SQL loops'
SET @num = @num + 1
IF @num = 3
BREAK
ELSE
CONTINUE
END;
GO


4. GOTO label

GOTO in SQL Server defines a label and it allows the code to jump onto that label from one section to another.

The syntax for GOTO

Define the label:
label:
Alter the execution:
GOTO label

Example

The example below shows how to use the GOTO statement in SQL Server. In the BEGIN...END block I declared two labels Test_1 and Test_2 that represent some access points if the given conditions are met. After reaching the first point labeled Test_1, go directly to the point labeled Test_3, skipping the point labeled Test_2.

USE model
GO
DECLARE @Count int;
SET @Count = 0;
WHILE @Count < 5
BEGIN
SELECT @Count
SET @Count = @Count + 1
IF @Count = 2 GOTO Test_1
IF @Count = 3 GOTO Test_2
END
Test_1: SELECT 'Test 1'
GOTO Test_3;
Test_2: SELECT 'Test 2';
Test_3: SELECT 'Test 3';
GO


5. IF...ELSE

IF-ELSE control flow statement executes a set of blocks in a way that if the condition mentioned in the IF parameter is True, the IF block executes, if it is False, then the ELSE block is executed.

The syntax for IF...ELSE

IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]

Example

The IF EXISTS condition checks if the select returns a record and if yes then displays YES, otherwise displays NO.

USE model
GO
IF EXISTS(SELECT Name FROM students WHERE name = 'Daniel')
PRINT 'YES'
ELSE
PRINT 'NO'
GO






6. RETURN

RETURN keyword exits the query or procedure unconditionally. It can also be used as an exit statement as statements after RETURN are not executed further.

The syntax for RETURN

RETURN [ integer_expression ]

Example

In the example below we have two inserts in the students table, between these two inserts is the T-SQL RETURN statement. The first insert will be inserted in the students table so that the RETURN statement ends the running of the BEGIN...END block. If we comment the SQL Server RETURN statement, then both rows will be inserted in the students table.

USE model
GO
BEGIN
INSERT INTO students(id, name, birthday, city)
VALUES (6, 'Tom', '1991-10-07', 'Boston');
RETURN
INSERT INTO students(id, name, birthday, city)
VALUES (7, 'Smith', '1990-09-15', 'New York');
END;
GO


7. THROW

THROW raise an exception and if a CATCH block is available, it allows the CATCH block to execute further.

The syntax for THROW

THROW

Example

In the following example, we will show how to use the THROW statement to handle possible errors that may occur in a SQL Server block. The first step is to create a table with a single column that contains the primary key constraint. The second step is to write the BEGIN TRY...END TRY block that contains the inserts in the created table, the first two inserts are unique, the third is a duplicate insert, and the last insert is unique. The third step, the BEGIN CATCH...END CATCH block catches and displays the error generated by the duplicate insert. The last step is to run the script that will produce the following result: the first two lines will be inserted, then the error Error in try catch block will be displayed.

USE model
GO
CREATE TABLE TestThrow
(ID INT PRIMARY KEY);
BEGIN TRY
INSERT TestThrow(ID) VALUES(1);
INSERT TestThrow(ID) VALUES(2);
INSERT TestThrow(ID) VALUES(1);
INSERT TestThrow(ID) VALUES(3);
END TRY
BEGIN CATCH
PRINT 'Error in try catch block';
THROW;
END CATCH;
GO


8. TRY...CATCH

TRY...CATCH block is used for error handling in T-SQL. If an error is encountered in the TRY block, the control is given to the CATCH block to handle the error.

The syntax for TRY...CATCH

BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH;

Example

The example below shows how to use the TRY...CATCH to handle errors in SQL Server. The select written in the BEGIN TRY...END TRY block generates the error, and with the help of the BEGIN CATCH...END CATCH block the error is caught. The error number is extracted and set in the declared variable @ErrorNumber. It is also useful to extract the error message that we set in the @ErrorMessage variable. To extract the error number and message, I used the SQL Server system functions ERROR_NUMBER and ERROR_MESSAGE.

USE model
GO
DECLARE @ErrorNumber int, @ErrorMessage varchar(2000);
BEGIN TRY
-- Generate divide-by-zero error.
SELECT 3/0;
END TRY
BEGIN CATCH
-- Catch error number and error message.
SELECT @ErrorNumber=ERROR_NUMBER();
SELECT @ErrorMessage=ERROR_MESSAGE();
PRINT @ErrorNumber
PRINT @ErrorMessage
END CATCH;
GO


9. WAITFOR

The execution of statements is paused for a specific time or interval if the WAITFOR keyword is stated.

The syntax for WAITFOR

WAITFOR
{
DELAY 'time_to_pass'
| TIME 'time_to_execute'
| [ ( receive_statement ) | ( get_conversation_group_statement ) ]
[ , TIMEOUT timeout ]
}

Example

In the following example, we will use WAITFOR TIME to insert a new record in the students table at a time specified by the user. The example also contains WAITFOR DELAY to delay the second insert in the students table. WAITFOR statement can also be used to execute functions or procedures at a given time.

USE model
GO
WAITFOR TIME '23:12:00';
INSERT INTO students(id, name, birthday, city)
VALUES (6, 'Tom', '1991-10-07', 'Boston');
WAITFOR DELAY '00:00:05'
INSERT INTO students(id, name, birthday, city)
VALUES (7, 'Smith', '1990-09-15', 'New York');
SELECT * FROM students;
GO


10. WHILE

The statements mentioned in the WHILE block are executed only if the condition mentioned in the WHILE parameter turns True. If it is False, the WHILE block doesn't execute further.

The syntax for WHILE

WHILE Boolean_expression
{ sql_statement | statement_block | BREAK | CONTINUE }

Example

The example uses an @count variable initially set with the value 1. Then it runs the WHILE statement as long as the value in @count is less than or equal to 5. Inside the BEGIN...END block, the @count variable is incremented by the value 1 to avoid the infinite running of the WHILE statement. When @count becomes greater than 5, the WHILE statement stops. When running the script, the While in SQL Server message will be displayed five times.

USE model
GO
DECLARE @count int
SET @count = 1
WHILE @count <= 5
BEGIN
PRINT 'While in SQL Server'
SET @count = @count + 1
END;
GO