• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 23
  • Last Modified:

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?
0
paultran00
Asked:
paultran00
1 Solution
 
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
 
Mark WillsTopic AdvisorCommented:
To answer your question... Var1 should be in scope for the duration of the procedure

Maybe post your procedure ?
1
 
HuaMin ChenSystem AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now