August 5, 2014 at 6:23 am
Apparently, while variable declaration occurs at compile time, inline assignment does not. Good to know.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
August 5, 2014 at 7:11 am
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
Yea, its like using a computed column in another computed column (simulating, the below gives error)
CREATE TABLE dbo.Products
(
ProductID int IDENTITY (1,1) NOT NULL
, QtyAvailable smallint
, UnitPrice money
, InventoryValue AS QtyAvailable * UnitPrice
, IncInventoryValue AS InventoryValue + 1
);
Msg 1759, Level 16, State 0, Line 1
Computed column 'InventoryValue' in table 'Products' is not allowed to be used in another computed-column definition.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
August 5, 2014 at 7:13 am
pmadhavapeddi22 (8/5/2014)
I am surprised to know that such a simple QOTD "FROM Hugo" !!! :w00t:
Agreed. Hugo usually gives something much more involved.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2014 at 7:19 am
SQLRNNR (8/5/2014)
pmadhavapeddi22 (8/5/2014)
I am surprised to know that such a simple QOTD "FROM Hugo" !!! :w00t:Agreed. Hugo usually gives something much more involved.
It's the holiday season for everyone 😎
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 7:35 am
Koen Verbeeck (8/5/2014)
SQLRNNR (8/5/2014)
pmadhavapeddi22 (8/5/2014)
I am surprised to know that such a simple QOTD "FROM Hugo" !!! :w00t:Agreed. Hugo usually gives something much more involved.
It's the holiday season for everyone 😎
Are you saying Christmas in August kind of thing?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2014 at 7:38 am
SQLRNNR (8/5/2014)
Koen Verbeeck (8/5/2014)
SQLRNNR (8/5/2014)
pmadhavapeddi22 (8/5/2014)
I am surprised to know that such a simple QOTD "FROM Hugo" !!! :w00t:Agreed. Hugo usually gives something much more involved.
It's the holiday season for everyone 😎
Are you saying Christmas in August kind of thing?
Right. Holiday has another connotation in English.
I meant vacation 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 7:53 am
Koen Verbeeck (8/5/2014)
SQLRNNR (8/5/2014)
Koen Verbeeck (8/5/2014)
SQLRNNR (8/5/2014)
pmadhavapeddi22 (8/5/2014)
I am surprised to know that such a simple QOTD "FROM Hugo" !!! :w00t:Agreed. Hugo usually gives something much more involved.
It's the holiday season for everyone 😎
Are you saying Christmas in August kind of thing?
Right. Holiday has another connotation in English.
I meant vacation 🙂
I see....Works both ways:cool:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2014 at 8:51 am
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
The problem is the line "DECLARE @i int = 1, @j-2 int = @i+1;".
The part dealing with @j-2 requires @i to be in scope; but although the assignment @i = 1 is valid (because it's tied to the declaration of @i) using @i to assign a value to @j-2 isn't, because @i is t scope only for its declaration and any assignment to @i which is tied to the declaration and any statement following the statement containing the declaration; so it isn't in scope for @j-2 = @i+1. The paser treats the whole line as being one statement for determining that @j-2=@i+1 isn't in a statement following the declaration of @i, but once it's decided the scope rules are being followed it treats the two assignments as two separate statements executed at run time, while the two declarations are executed at parse time). This is conceptually rather sloppy, but that's not unusual for SQL.
Tom
August 5, 2014 at 9:34 am
Nice question, thanks.
August 5, 2014 at 9:59 am
Hany Helmy (8/5/2014)
Thanx 4 the easy 2 pts.
+1
August 5, 2014 at 3:29 pm
Koen Verbeeck (8/5/2014)
Nice question Hugo.Your explanation left out "compound assignment operator" however 🙂
http://technet.microsoft.com/en-us/library/ms189484(v=sql.100).aspx
Thans for the addition, Koen!
I only realise now (after reading your message and another on the same topic) that I had used this syntax without as much as a second thought, because I am now very used to using it. But it was introduced in the same version as inline variable assignment, so I guess this was a bit silly! 😉
August 5, 2014 at 3:31 pm
Raghavendra Mudugal (8/5/2014)
I executed the script in 2008R2, and I re-read the question and see there is no R2, but still went with the gut feeling that this must work, and yes, it does
Ouch! My intention was to write "SQL 2008 or higher", but upon rereading I see I left out the last part.
Sorry!
For the record: both this question and the followup (called, creatively, "In-line variable assignment 2") work the same on SQL Server 2008 and every version since.
August 5, 2014 at 3:34 pm
TomThomson (8/5/2014)
edit: I'm amazed how many people fell for one of the two "only simple values, no expressions" options; and that only 44% got it right.
I am not surprised by that; I intended it to be a strong detractor. A lot of people have only seen this syntax used with a simple constant. (And frankly, I would never use it like this in production code; I only use this syntax for constants as a starting value, or very simple expressions such as CURRENT_TIMESTAMP. If the starting value if an expression of any complexity, or even a parameter passed in, I'll use explicit assignment. When browsing code, people tend to skim over the DECLARE statements, so they might miss when a complex expression is used there.
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply