Variables and Control Flow

Analogy categoryAnalogies

Declaring and Assigning Variables

A T-SQL variable is declared with DECLARE @name datatype and lives only for the duration of the batch or the procedure it is defined in; once the batch ends, the variable and its value disappear. You can assign a value at declaration time with an inline initializer (DECLARE @total INT = 0), or afterward using SET for a single scalar assignment, or SELECT when you want to pull a value out of a query into the variable in the same statement.

🏏

Cricket analogy: Declaring @runsScored INT = 0 before an over is like a scorer zeroing the counter on the electronic scoreboard at the MCG before Virat Kohli faces the first ball, ready to accumulate runs as the over unfolds.

Branching with IF...ELSE

T-SQL's IF statement evaluates a Boolean expression and executes the single statement (or BEGIN...END block) that follows; an optional ELSE branch runs when the condition is false. Because IF only controls one statement by default, wrapping multi-statement logic in BEGIN...END is essential — omitting it is a common bug where only the first line after IF is actually conditional and everything after it always executes.

🏏

Cricket analogy: IF @wicketsLost >= 9 BEGIN ... END mirrors a captain's decision tree: if nine wickets are down, send in the tailender to block rather than swing, otherwise let the recognized batter play freely.

sql

DECLARE @counter INT = 1;
DECLARE @maxRetries INT = 5;
DECLARE @status VARCHAR(20);

WHILE @counter <= @maxRetries
BEGIN
    SELECT @status = JobStatus FROM dbo.JobQueue WHERE JobId = 42;

    IF @status = 'Success'
    BEGIN
        PRINT 'Job completed on attempt ' + CAST(@counter AS VARCHAR(10));
        BREAK;
    END
    ELSE IF @status = 'Skip'
    BEGIN
        SET @counter += 1;
        CONTINUE;
    END

    SET @counter += 1;
END

Looping with WHILE, BREAK, and CONTINUE

WHILE repeats a block for as long as its Boolean condition stays true, which makes it the only native looping construct in T-SQL — there is no FOR or FOREACH. Inside the loop, BREAK exits immediately regardless of the condition, while CONTINUE skips the rest of the current iteration and jumps back to re-evaluate the WHILE condition; forgetting to update the loop variable before CONTINUE is a classic way to write an infinite loop.

🏏

Cricket analogy: WHILE @oversBowled < 20 mirrors a T20 innings continuing over after over until the fixed limit is reached, with BREAK acting like a rain-shortened match called off early by the umpires.

Variables declared inside a stored procedure or batch are not visible outside it — there is no global variable scope in T-SQL beyond session-level context like SESSION_CONTEXT() or temp tables. If you need to pass a value between batches, use an OUTPUT parameter, a temp table, or SESSION_CONTEXT().

Always wrap multi-statement IF and WHILE bodies in explicit BEGIN...END blocks. Without them, only the single statement immediately following IF or WHILE is conditional/looped, and subsequent statements silently execute unconditionally every time — a frequent source of logic bugs that SQL Server will not warn you about.

DECLARE creates a batch-scoped variable; SET assigns one scalar value, SELECT can assign from a query result.
Variables lose their value and scope at the end of the batch, procedure, or trigger they were declared in.
IF/ELSE branches on a Boolean expression and only controls the single statement or BEGIN...END block that follows it.
WHILE is T-SQL's only native loop construct — there is no FOR or FOREACH keyword.
BREAK exits a WHILE loop immediately; CONTINUE skips to the next condition check.
Forgetting to update the loop counter before CONTINUE is a common cause of infinite loops.
Compound assignment operators like += are supported for variables, e.g. SET @counter += 1.

Practice what you learned

1. What happens to a variable declared inside a stored procedure once the procedure finishes executing?

2. Which statement is true about IF without an explicit BEGIN...END block?

3. What is the only native iterative looping construct in T-SQL?

4. Inside a WHILE loop, what does CONTINUE do?

5. Which of these correctly assigns a value pulled from a table column into a variable?

Was this page helpful?

Stored Procedures

Related Study Notes in Programming

Browse all study notes

Programming

Apache Spark Study Notes

Programming · 30 topics

Programming

Apache Flink Study Notes

Programming · 30 topics

Programming

Hadoop Study Notes

Programming · 30 topics

Programming

Snowflake Study Notes

Programming · 30 topics

Programming

Apache Airflow Study Notes

Programming · 30 topics

Programming

dbt (Data Build Tool) Study Notes

Programming · 30 topics

Topics covered

#Programming#SQLServerTSQLStudyNotes#VariablesAndControlFlow#Variables#Control#Flow#Declaring#StudyNotes#SkillVeris#ExamPrep

Declaring and Assigning Variables

Branching with IF...ELSE

Looping with WHILE, BREAK, and CONTINUE

Practice what you learned

You May Also Like

Related Reading

Related Study Notes in Programming

Topics covered