Variables and Control Flow

This section describes variables and control-flow language elements in Transact-SQL (T-SQL). These elements allow you to write more dynamic and powerful SQL statements and stored procedures.

Variables

Variables are used to store data temporarily within a T-SQL batch or stored procedure. They are declared using the DECLARE statement and assigned values using the SET or SELECT statement.

Declaring Variables

To declare a variable, you must specify its name (prefixed with an @ symbol) and its data type.

DECLARE @MyVariable INT;
DECLARE @CustomerName VARCHAR(100), @OrderDate DATE;
            

Assigning Values to Variables

You can assign a single value to a variable using SET:

SET @MyVariable = 10;
SET @CustomerName = 'Acme Corporation';
SET @OrderDate = '2023-10-27';
            

You can also assign values from a query using SELECT:

SELECT @MyVariable = COUNT(*)
FROM Orders
WHERE CustomerID = 1;

SELECT @CustomerName = Name, @OrderDate = OrderDate
FROM Customers
WHERE CustomerID = 1;
            

Control Flow Language

T-SQL provides several constructs to control the flow of execution within a batch or stored procedure. These include conditional statements and loops.

Conditional Statements

IF...ELSE statements allow you to execute different blocks of code based on a specified condition.

IF EXISTS (SELECT 1 FROM Products WHERE ProductName = 'Chai')
BEGIN
    PRINT 'Product "Chai" exists.';
END
ELSE
BEGIN
    PRINT 'Product "Chai" does not exist.';
END
                

Looping Constructs

T-SQL supports WHILE loops for repeating a block of code as long as a specified condition is true.

DECLARE @Counter INT = 1;
WHILE @Counter <= 5
BEGIN
    PRINT 'Counter is: ' + CAST(@Counter AS VARCHAR);
    SET @Counter = @Counter + 1;
END
                

BREAK and CONTINUE

Within a WHILE loop, you can use:

• BREAK: To exit the loop immediately.
• CONTINUE: To skip the rest of the current iteration and proceed to the next.

GOTO Statement

The GOTO statement transfers control to another statement in the same procedure or batch. Its use is generally discouraged in favor of structured programming constructs.

DECLARE @Count INT = 0;
LoopStart:
SET @Count = @Count + 1;
IF @Count < 3
    GOTO LoopStart;
PRINT 'Loop finished.';
                

WAITFOR Statement

The WAITFOR statement pauses the execution of a batch or stored procedure for a specified time interval or until a specific time.

-- Wait for 5 seconds
WAITFOR DELAY '00:00:05';

-- Wait until 2:30 PM
WAITFOR TIME '14:30:00';