December 7, 2009 at 8:34 am
This burned me when declaring a table variable inside a loop and the table kept getting bigger and bigger each time through...this is also true of non-table variables.
Normally, if you declare something twice, you get an error :
DECLARE @testString VARCHAR(10);
DECLARE @testString VARCHAR(10);
Like this :
Msg 134, Level 15, State 1, Line 3
The variable name '@testString' has already been declared. Variable names must be unique within a query batch or stored procedure.
But if you declare the variable inside a loop, you don't get the error - it appears to ignore that line entirely, so the following code :
DECLARE @counter INT;
SET @counter = 0;
WHILE @counter < 5
BEGIN
DECLARE @testString VARCHAR(10);
SELECT @testString = COALESCE(@testString,'') + 'X';
PRINT @testString;
SET @counter = @counter + 1;
END;
does not produce an error and produces this output :
X
XX
XXX
XXXX
XXXXX
I'm not sure if this is intended behavior or not in sql server, but thought I'd share in case anyone else hits this.
I first noticed this in SQL 2005 but it also happens in 2000 and 2008.
December 7, 2009 at 11:54 pm
Good to know, I suppose, but why are you declaring the variables inside a loop? That seems like an odd practice to me, and I just wondered about your reasons for doing it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 9, 2009 at 9:00 pm
The whole reason for the loop was to break up a large insert process into batches, which in the past has performed better. I declared the variable (in the original case, a table variable) in the loop for a couple of reasons : 1) I could get away with that in java or vb due to differences in variable scope; 2) I made an assumption where making a new table variable and inserting into it might be quicker than deleting from an existing one (since you can't truncate a table variable) which I didn't bother testing at the time. That's what I get for being in a hurry.
December 9, 2009 at 10:44 pm
I understand. Thanks for taking the time to reply.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 11, 2009 at 11:05 am
The string length behavior is normal. adding another SELECT to initialize the variable for each loop iteration produces the correct results !
DECLARE @counter INT;
SET @counter = 0;
WHILE @counter < 5
BEGIN
DECLARE @testString VARCHAR(10);
SELECT @testString=NULL
SELECT @testString = COALESCE(@testString,'') + 'X';
PRINT @testString;
SET @counter = @counter + 1;
END;
As for the declare issue - I dunno
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply