Post reply

Local variable and Try/Catch conundrum

  • November 16, 2012 at 3:04 pm

    #265987

    Hello All,

    I understand that the scope of a local variable/table variable declared inside a TRY block is available to the CATCH block. But what baffles me is the following situation:

    BEGIN TRY

    -- Try to select from a variable that's not declared yet

    -- This produces an error

    --SELECT @test-2 'TestVar';

    -- Induce an error statement that will fail the batch

    DECLARE @Var INT = 1/0;

    -- Declare a variable and initialize it

    DECLARE @test-2 VARCHAR(100) ;

    SET @test-2 = 'abc' ;

    -- Try a table vairable

    DECLARE @tab TABLE ( Dt DATE DEFAULT GETDATE()) ;

    -- Try temp table

    CREATE TABLE #Tab ( Dt DATE DEFAULT GETDATE()) ;

    -- proof that no other statment other than DECLARE is working

    SELECT 'a' AS JunkValue;

    END TRY

    BEGIN CATCH

    -- Try selecting a variable that was declared in TRY block

    -- but that is declared after the error-inducer.

    SELECT @test-2 'TestVariable' ;

    -- Select table variable

    SELECT * FROM @tab ;

    -- Select temp table

    -- This produces error

    -- SELECT * FROM #Tab

    SELECT ERROR_MESSAGE() ErrorMessage;

    END CATCH

    Its obvious from the code that @test is declared after the error-inducer. The execution of the statements will stop at "select 1/0" , but why am i still able to select @test-2 in the CATCH block ?

    If my guess is correct, then all the DECLARE statements are compiled first and everything else will happen per sequence after that. Right?

    Also, per my code above, i can not use a variable that has not been declared before ( i tried to do a SELECT @test-2 at the start of code). So there is a twist to my guess, right?

    Any thoughts ?

  • November 16, 2012 at 4:55 pm

    #1559959

    The TRY block will be fully parsed and begin execution before the CATCH block is processed, so, yes, all variables in the TRY block will be made available to the CATCH block.

    General SQL rules prevent an undeclared variable from being referenced in any (non-CATCH) code, so, no, you cannot reference a variable in the TRY block before it is DECLAREd, just as you cannot reference a variable in non-TRY T-SQL before it's DECLAREd.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply