Variables, Control Flow & TRY/CATCH

T-SQL is a procedural language as well as a query language. Variables, conditionals, loops, and structured error handling let you write algorithms that run inside the engine.

Variables

Declare with DECLARE and assign with SET or SELECT:

DECLARE @name        NVARCHAR(100) = N'Steven';   -- declare + initialise
DECLARE @salary      DECIMAL(10,2);
DECLARE @hire_date   DATE = GETDATE();

SET @salary = 9000;          -- single value, ANSI-compliant
SELECT @salary = salary      -- can pull from a query — beware multi-row
FROM   hr.employees
WHERE  employee_id = 100;

PRINT @name + ' earns ' + CAST(@salary AS NVARCHAR(20));

SET vs SELECT for Assignment

DECLARE @sal DECIMAL(10,2) = 0;

-- If the query returns no rows, @sal stays at 0 (silent!)
SELECT @sal = salary FROM hr.employees WHERE employee_id = 99999;
PRINT @sal;     -- 0

-- SET would set it to NULL via a subquery
SET @sal = (SELECT salary FROM hr.employees WHERE employee_id = 99999);
PRINT @sal;     -- NULL

SELECT for assignment is faster when you're populating multiple variables from a single query, but SET is safer for the no-rows case.

Table Variables

Lightweight, schema-bound, statement-scoped temp store:

DECLARE @top_earners TABLE (
    employee_id INT PRIMARY KEY,
    name        NVARCHAR(100),
    salary      DECIMAL(10,2)
);

INSERT INTO @top_earners (employee_id, name, salary)
SELECT TOP 10 employee_id, first_name + ' ' + last_name, salary
FROM   hr.employees
ORDER BY salary DESC;

SELECT * FROM @top_earners;

Conditionals: IF / ELSE

DECLARE @count INT = (SELECT COUNT(*) FROM hr.employees);

IF @count > 100
    PRINT 'Large team';
ELSE IF @count > 10
    PRINT 'Mid-sized team';
ELSE
BEGIN
    PRINT 'Small team';
    PRINT 'Consider hiring';
END;

BEGIN ... END groups multiple statements — like { } in C-style languages.

Loops: WHILE

DECLARE @i INT = 1;

WHILE @i <= 10
BEGIN
    PRINT 'Iteration ' + CAST(@i AS NVARCHAR(2));
    SET @i += 1;

    IF @i = 7
        BREAK;          -- exit the loop early

    IF @i % 2 = 0
        CONTINUE;       -- skip the rest of the body
END;

T-SQL has only one looping construct — WHILE. BREAK exits, CONTINUE jumps to the top.

GOTO

GOTO exists but is rarely the right tool — most uses are clearer with IF/ELSE or structured exception handling. Brief example:

DECLARE @ok BIT = 1;
IF @ok = 0 GOTO err_handler;
PRINT 'Ok';
RETURN;

err_handler:
    PRINT 'Error path';

Structured Error Handling: TRY / CATCH

Wrap fallible code in BEGIN TRY ... END TRY followed by BEGIN CATCH ... END CATCH. The CATCH block runs only if an error of severity 11–19 occurs in the TRY block.

BEGIN TRY
    BEGIN TRAN;

    UPDATE hr.accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE hr.accounts SET balance = balance + 100 WHERE id = 2;

    -- Force an error to demo CATCH
    DECLARE @x INT = 1 / 0;

    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN;

    DECLARE @err NVARCHAR(2048) =
        N'[' + CAST(ERROR_NUMBER() AS NVARCHAR(10)) + N'] ' +
        ERROR_MESSAGE() +
        N' (line ' + CAST(ERROR_LINE() AS NVARCHAR(10)) + N')';

    PRINT @err;
    THROW;       -- rethrow to caller, preserves stack
END CATCH;

Available Error Functions Inside CATCH

THROW vs RAISERROR

T-SQL has two ways to raise errors. THROW is preferred for new code (SQL Server 2012+).

-- Raise a custom error
THROW 50001, 'Invalid input: salary must be > 0', 1;

-- Re-throw the current error inside a CATCH (no arguments)
BEGIN CATCH
    THROW;
END CATCH;

-- RAISERROR (legacy) — supports format strings
DECLARE @sal DECIMAL(10,2) = -5;
RAISERROR ('Salary %s is invalid', 16, 1, CAST(@sal AS VARCHAR(20)));

-- RAISERROR with NOWAIT flushes immediately — useful for progress messages
RAISERROR ('Step 1 of 3 complete', 10, 1) WITH NOWAIT;

THROW always severity 16 means it always triggers CATCH. Severities < 11 from RAISERROR are warnings — they do not trigger CATCH blocks.

Combined Pattern

CREATE PROCEDURE hr.sp_transfer_funds
    @from_id INT,
    @to_id   INT,
    @amount  DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @amount <= 0
        THROW 50100, 'Amount must be positive', 1;

    BEGIN TRY
        BEGIN TRAN;

        UPDATE hr.accounts SET balance = balance - @amount WHERE id = @from_id;
        IF @@ROWCOUNT = 0 THROW 50101, 'Source account not found', 1;

        UPDATE hr.accounts SET balance = balance + @amount WHERE id = @to_id;
        IF @@ROWCOUNT = 0 THROW 50102, 'Destination account not found', 1;

        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;     -- rethrow original error to caller
    END CATCH;
END;

Best Practices

• Default to SET XACT_ABORT ON; SET NOCOUNT ON; at the top of every procedure.
• Always check @@TRANCOUNT > 0 before ROLLBACK in CATCH.
• Prefer THROW over RAISERROR for new code; use the bare THROW; form to rethrow inside CATCH.
• Avoid loops for row-by-row DML — write set-based statements instead.
• Use table variables for very small, tightly scoped result sets; switch to #temp tables for larger work or when statistics matter.

Summary

• DECLARE + SET/SELECT for variables; BEGIN ... END groups statements.
• IF/ELSE and WHILE are the structural control flow; BREAK/CONTINUE/GOTO are escape hatches.
• TRY/CATCH catches severity 11–19 errors; ERROR_*() functions describe the failure.
• THROW is the modern way to raise / rethrow; RAISERROR remains for format strings and WITH NOWAIT progress.