July 29, 2014 at 8:13 pm
Comments posted to this topic are about the item table variable declaration inside a loop
July 29, 2014 at 9:51 pm
A little more info on declaring variables (scalar and table variables) - whilst the example in this question is quite right and the loop does not attempt to create another copy of the table variable, you must declare variables before you attempt to use them
If the code is adjusted so to be as follows...
set @I= 1
DECLARE @i INT
WHILE @i < 5
BEGIN
SET @i = @i + 1
INSERT INTO @j-2
SELECT @i
END
DECLARE @j-2 AS TABLE
(
i INT
)
SELECT * FROM @j-2
you end up with 2 errors because there is an attempt to assign a value to @i before it is declared and an attempt to insert into @j-2 before it is declared.
July 30, 2014 at 12:27 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 30, 2014 at 1:58 am
For me the way how SQL Server handles the declaration of the variables is a bit strange.
This piece of code doesn't return an error, even though the declaration of @j-2 doesn't seem to happen.
DECLARE @i INT = 1
WHILE @i < 0 -- While loop will not be entered
BEGIN
PRINT 'DECLARE @j-2'
DECLARE @j-2 AS TABLE
(
i INT
)
SET @i = @i + 1
INSERT INTO @j-2
SELECT @i
END
SELECT * FROM @j-2
July 30, 2014 at 3:43 am
This was removed by the editor as SPAM
July 30, 2014 at 4:20 am
This was removed by the editor as SPAM
July 30, 2014 at 5:16 am
Always nice to learn something new - good question. And Mighty's example code a few posts ago is just as interesting.
July 30, 2014 at 5:16 am
July 30, 2014 at 5:30 am
Stewart "Arturius" Campbell (7/30/2014)
All variables are declared at compile time. at this time, all control flow commands are ignored.
That is more or less what I was thinking, but in that case it should not matter when you are declaring a variable and when you are actually using it.
July 30, 2014 at 6:01 am
Hmmm... Learned something today.
Good question. Thanks!
---------------
Mel. 😎
July 30, 2014 at 6:28 am
Mighty (7/30/2014)
Stewart "Arturius" Campbell (7/30/2014)
All variables are declared at compile time. at this time, all control flow commands are ignored.That is more or less what I was thinking, but in that case it should not matter when you are declaring a variable and when you are actually using it.
It does matter because the compiler uses a single pass over the batch text. It starts at the top, and proceeds downwards towards the bottom, keeping track of variable declarations it encounters. When a variable is referenced, the compiler can only know what to do with it if it has already registered the declaration. Which means that all declarations must be "before" the first use when scanning the text top to bottom and left to right. Regardsless of control flow statements that might change the order in which statements are executed.
July 30, 2014 at 6:31 am
Brian.Klinect (7/30/2014)
If you're allowed to re-declare @j-2 inside the while loop, why are you allowed to select from @j-2 outside of the while loop?
See my post above (which I started writing before your post was made). The declare is only used once, durinig the compile phase. It is then ignored when the loop executes, so there is no "re-declaration".
July 30, 2014 at 6:37 am
Hugo Kornelis (7/30/2014)
Brian.Klinect (7/30/2014)
If you're allowed to re-declare @j-2 inside the while loop, why are you allowed to select from @j-2 outside of the while loop?See my post above (which I started writing before your post was made). The declare is only used once, durinig the compile phase. It is then ignored when the loop executes, so there is no "re-declaration".
Maybe Hugo could be turned loose to redo the BOL's so they make sense? 😉
Thanks to OP for question.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
July 30, 2014 at 6:39 am
Mighty (7/30/2014)
Stewart "Arturius" Campbell (7/30/2014)
All variables are declared at compile time. at this time, all control flow commands are ignored.That is more or less what I was thinking, but in that case it should not matter when you are declaring a variable and when you are actually using it.
Good point! I'm guessing that the tasks of declaring variables and checking that variables are declared are done in the same pass. ('Pass' is what each scan of programming code or t-sql in this case is called, although sometimes the task of scanning an intermediate or target format of the program might be called a 'pass' [citation needed LOL].) So if those two tasks are in a single pass, and a pass happens from top to bottom, even ignoring control, then declarations need to happen before references.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply