A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Wednesday, March 22, 2017

Variable scoping in TSQL isn't a thing

It's a pop quiz kind of day: run the code through your mental parser.

BEGIN TRY
    DECLARE @foo varchar(30) = 'Created in try block';
    DECLARE @i int = 1 / 0;
END TRY
BEGIN CATCH
    PRINT @foo;
    SET @foo = 'Catch found';
END CATCH;

PRINT @foo;
It won't compile since @foo goes out of scope for both the catch and the final line
It won't compile since @foo goes out of scope for the final line
It prints "Created in try block" and then "Catch found"
I am too fixated on your form not having a submit button

Crazy enough, the last two are correct. It seems that unlike every other language I've worked with, all variables are scoped to the same local scope regardless of where in the script they are defined. Demo the first

Wanna see something even more crazy? Check this version out

BEGIN TRY
    DECLARE @i int = 1 / 0;
    DECLARE @foo varchar(30) = 'Created in try block';
END TRY
BEGIN CATCH
    PRINT @foo;
    SET @foo = 'Catch found';
END CATCH;

PRINT @foo;

As above, the scoping of variables remains the same but the forced divide by zero error occurs before the declaration and initialization of our variable @foo. The result? @foo remains uninitialized as evidenced by the first print in the Catch block but it still exists/was parsed to instantiate the variable but not so the value assignment. Second demo

What's all this mean? SQL's weird.

2 comments:

Eric said...

You got me laughing to myself with option 4 regarding the lack of a submit button. Thank you!

JonathanR said...

It makes sense for Microsoft to implement it like this as if @foo didn't exist then the catch would also throw an error.