"if" statement, i'm having problems 1

[henin]

Hi everyone,
My procedure recieves one argument, "myNumber".
I'd like to declare as many variables as "myNumber"'s value as follows:

If (@myNumber=1) DECLARE @firstName VARCHAR(20)
IF (@myNumber=2) 
BEGIN
 DECLARE @firstName VARCHAR(20)
 DECLARE @secondName VARCHAR(20)
END

When i run the above code i get an error indicating that at line 4, @firstname has already been declared.
Can anyone tell me where i went wrong ?
Thanks

 

[philhege]

It wouldn't hurt to declare both variables in any case. Have your processing logic use the parameter value as a means of branching, e.g.

DECLARE @firstName VARCHAR(20)
DECLARE @secondName VARCHAR(20)
If (@myNumber=1) DECLARE @firstName VARCHAR(20)
IF (@myNumber=2)
BEGIN
--Do stuff with both variables
END

IF (@myNumber=1)
BEGIN
--Do stuff with one variable
END

The reason you're getting the error is that the parser doesn't "know" that the IF condition will make a difference in processing, so it evaluates the entire script as a block. Since variables may be DECLAREd only once, that's why you get the error.

I had a similar situation a couple of years ago, but I can't find the resolution right now. I still think you can take the approach I modeled above.

HTH,



Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[philhege]

Ack. Corrected code:

DECLARE @firstName VARCHAR(20)
DECLARE @secondName VARCHAR(20)

IF (@myNumber=2)
BEGIN
--Do stuff with both variables
END

IF (@myNumber=1)
BEGIN
--Do stuff with one variable
END

 

[henin]

Thanks phil,
For the explanation and solution