November 16, 2012 at 3:04 pm
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
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply