Is the scope of the Var1 only in the BEGIN TRY...END TRY block and not the BEGIN CATCH...END CATCH block?

Hi, I'm using SQL SERVER 2008R2.

When I pass in a parameter Var1 to a stored procedure, inside a BEGIN TRY...END TRY,   PRINT Var1   works.
-----------------------
Now I want to test error-trapping.
I have this statement in the BEGIN TRY...END TRY:

SELECT 1/0 AS JUNK

However, inside the BEGIN CATCH...END CATCH,   PRINT Var1   shows nothing.

QUESTION:  Is the scope of the Var1  only in the BEGIN TRY...END TRY block and not the BEGIN CATCH...END CATCH block?
paultran00Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
You don't post your code, but if you define a stored proc like this...
CREATE PROCEDURE vartrycatchtest
	-- Add the parameters for the stored procedure here
	@Var1 int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    Begin Try
      Declare @Var2 integer
      Set @Var2 =2

      Print @Var1
      Print @Var2
      SELECT 1/0 AS JUNK
    End Try 
    Begin Catch
      Print Error_Message()
      Print @Var1
      Print @Var2
    End Catch
END
GO

Open in new window

...and then execute it like this...
exec vartrycatchtest 1

Open in new window

...@Var1 and @Var2 are both printed in both blocks.

Bye. Olaf.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
To answer your question... Var1 should be in scope for the duration of the procedure

Maybe post your procedure ?
1
HuaMin ChenProblem resolverCommented:
No, Var1 should be within catch part.

The reason can be due to that Var1 has not been assigned with one value properly.
0
Olaf DoschkeSoftware DeveloperCommented:
@Var1 is defined as a parameter, so it is in scope for the duration of the whole procedure. Which makes it exist in the catch block. That's what Mark is saying.

The point about no assignment to @Var1 also can't be true. In my example, I gave it a default value 0, which is overridden by what you pass in. But even if you remove that default you still can't have a non assigned parameter, as not passing it in then causes error 201, without a default value it's not optional to pass in a value. The value could be whitespace, that's the worst-case scenario.

Bye, Olaf.
1
Scott PletcherSenior DBACommented:
@Var1 likely contains NULL -- you don't see any output when you print a NULL value.  By default a variable will be NULL if you didn't assign a value to it.  A NULL can also be explicitly passed into a parameter when executing a stored proc, either accidentally or on purpose.  For example:

DECLARE @var1 date
PRINT @var1
EXEC dbo.proc1 @var1

Since @var1 hasn't been assigned a value, it won't print and NULL will be passed to proc1.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.