26

I would like to declare a variable within an if/else statement in a SQL Server stored procedure. I understand that this is fairly impossible because SQL Server doesn't do memory management with respect to declaration of variables within procedures. Is there a way to have a variable scoped in an if/else statement, then redeclare a variable with the same name in another if/else statement? For example:

create procedure Foo as begin if exists (x) begin declare @bob int set bob = 1 end else begin declare @bob int set bob = 2 end end
Improve this question

4 Answers 4

Reset to default
32

From books online:

The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.

However. Nothing keeps you from doing this:

create procedure Foo as begin declare @bob int if exists (x) begin set @bob = 1 end else begin set @bob = 2 end end
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

9

No, SQL is pretty funny/weird like that

Declare the variable before the if exists block of code

so

declare @bob int set @bob = 2 if exists(x) begin set @bob = 1 end

Now, take a look at these examples and try to guess what happens

WHILE 1 = 2 --not true of course BEGIN DECLARE @VAR INT; END SET @VAR = 1; SELECT @VAR;

This of course works, but it is not initialized every time

DECLARE @loop INT SET @loop = 0 WHILE @loop <=6 BEGIN DECLARE @VAR INT SET @VAR = COALESCE(@VAR,0) + 1 SET @loop = @loop +1 END SELECT @VAR
Improve this answer

1 Comment

I'm scratching my head over your WHILE 1 = 2 example... how???
3

is there some reason why you can't do :

declare @bob int if exists(x) begin set @bob = 1 end else begin set @bob = 2 end
Improve this answer

4 Comments

Exactly what I was going to say. Sounds like OP is only trying to do this for the sake of formatting.
I think it's a bad particular example on the OP's point, there are certainly cases where a variable isn't leaving the scope of BEGIN/END and you may want to re-use a variable name within separate and restrictive scopes, and unfortunately this simply isn't available.
That is what i am currently doing. There isn't a technical reason why I want to do it the way I mentioned. I was wondering if it was possible.
@Yuck. Not just for formatting. I was working with some .net code and was curious if there was a way to scope variables in sql. Found out I didn't know if there is a possible way of doing it. I assumed there wasn't a way but i didnt know for fact or why you cant.
2

You could resort to using dynamic SQL:

if exists (x) begin exec sp_executesql N' declare @bob int set @bob = 1 '; end else begin exec sp_executesql N' declare @bob int set @bob = 2 '; end
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.