August 5, 2014 at 3:37 pm
Carlo Romagnano (8/5/2014)
A little variation:
-- this runs
DECLARE @i int = 1, @j-2 int = 2;
SET @i += 2;
SELECT @j-2 * @i
GO
-- this raises error
DECLARE @i int = 1, @j-2 int = @i + 1;
SET @i += 2;
SELECT @j-2 * @i
Interesting find, Carlo! I was not aware of that.
I see that Tom already posted an explanation. I think he is correct, but I will phrase it simpler - my explanation is that a variable is available for use in all statements following the DECLARE statement. Here, the assignment to @j-2 is part of its DECLARE which again is part of the DECLARE for @i - part of, not following.
August 5, 2014 at 3:40 pm
And to conclude my rapidfire of messages - thanks all for the kind messages.
This question was indeed easier than some of my others. My goal with this question (and the followup) was to showcase how exactly this feature works, not to make people's lives hard.
I am glad that so many people liked my question! 😉
August 5, 2014 at 11:47 pm
Revenant (8/5/2014)
Hany Helmy (8/5/2014)
Thanx 4 the easy 2 pts.+1
+1
Thanks
August 6, 2014 at 8:00 am
Great question! It can be used in several different ways to learn more about variables and assignments, and versions of SQL Server. Thanks (even though I got it wrong 🙂 ).
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 6, 2014 at 2:16 pm
Hugo Kornelis (8/5/2014)
I see that Tom already posted an explanation. I think he is correct, but I will phrase it simpler - my explanation is that a variable is available for use in all statements following the DECLARE statement. Here, the assignment to @j-2 is part of its DECLARE which again is part of the DECLARE for @i - part of, not following.
I'm looking for a simple explanation that allows the assignment to not be part of the declaration so that I can account for the assignment being executed each time round a loop while the declaration isn't. It would be conceptually much simpler if either the assignment was only executed once or a duplicate declaration was acceptable. I actually would prefer your simpler version if I didn't worry about it misleading people into thinking the assignment only happened once.
Tom
August 6, 2014 at 2:31 pm
TomThomson (8/6/2014)
Hugo Kornelis (8/5/2014)
I see that Tom already posted an explanation. I think he is correct, but I will phrase it simpler - my explanation is that a variable is available for use in all statements following the DECLARE statement. Here, the assignment to @j-2 is part of its DECLARE which again is part of the DECLARE for @i - part of, not following.
I'm looking for a simple explanation that allows the assignment to not be part of the declaration so that I can account for the assignment being executed each time round a loop while the declaration isn't. It would be conceptually much simpler if either the assignment was only executed once or a duplicate declaration was acceptable. I actually would prefer your simpler version if I didn't worry about it misleading people into thinking the assignment only happened once.
Fair enough. Let me try it differently.
During the parse phase, a single pass is made. For this phase, the line "DECLARE @i int = 1, @j-2 int = @i + 1;" is considered a single statement - a DECLARE statement that happens to also include some assigments. This statement fails the parse checks, because it contains a reference to a variable (@i) that was not yet declared before this statement was seen in the single parse pass.
Had the statement been "DECLARE @i int = 1, @j-2 int = @x + 1;", and had @x been declared before this statement, then this would have been okay,
After the parse, pure DECLARE statements are removed (the information in them is encoded in internal structures set up to hold the variables), and DECLARE statements with inline assignment are stripped of the DECLARE part but the assignment is retained as an executable statement. So the above single statement is conceptually seen as
-- DECLARE @i int, @j-2 int; (Included as comment - not longer a statement because it is already encoded in internal structures)
SELECT @i = 1, @j-2 = @x + 1;
And the control-flow around this block of code governs how often this assignment statement is executed.
(PS: Interesting exercise - copy/paste the two below blocks of procedural SQL in SSMS, then hit the "estimated execution plan" button)
DECLARE @x int;
SET @x = 1;
DECLARE @i int, @j-2 int;
SELECT @i = 1, @j-2 = @x + 1;
DECLARE @z int;
GO
DECLARE @x int = 1;
DECLARE @i int = 1, @j-2 int = @x + 1;
DECLARE @z int;
GO
August 6, 2014 at 4:38 pm
Hugo Kornelis (8/6/2014)
Fair enough. Let me try it differently.During the parse phase, a single pass is made. For this phase, the line "DECLARE @i int = 1, @j-2 int = @i + 1;" is considered a single statement - a DECLARE statement that happens to also include some assigments. This statement fails the parse checks, because it contains a reference to a variable (@i) that was not yet declared before this statement was seen in the single parse pass.
Had the statement been "DECLARE @i int = 1, @j-2 int = @x + 1;", and had @x been declared before this statement, then this would have been okay,
After the parse, pure DECLARE statements are removed (the information in them is encoded in internal structures set up to hold the variables), and DECLARE statements with inline assignment are stripped of the DECLARE part but the assignment is retained as an executable statement. So the above single statement is conceptually seen as
-- DECLARE @i int, @j-2 int; (Included as comment - not longer a statement because it is already encoded in internal structures)SELECT @i = 1, @j-2 = @x + 1;
And the control-flow around this block of code governs how often this assignment statement is executed.
Yes, that's a really good and clear explanation. Thanks, Hugo.
edit; fix [/quote] tag
Tom
August 11, 2014 at 5:15 am
Easy, very Easy
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply