July 21, 2011 at 8:45 am
So I found this link (http://technet.microsoft.com/en-us/library/cc721270(SQL.100).aspx) and have a question about the Inline Declarations.
The link says:
Microsoft SQL Server® 2008 enables you to initialize variables inline as part of the variable declaration statement instead of using separate DECLARE and SET statements. This enhancement helps you abbreviate your code. The following code example demonstrates inline initializations using a literal and a function:
DECLARE @i AS INT = 0, @d AS DATETIME = CURRENT_TIMESTAMP;
Which is all well and good (I like this enhancement), but does anyone know if you can do this with an actual SELECT statement?
Say I want to set my variable to a value from a table.
Declare @MyVar INT = Select Max(MyID) from dbo.MyTable;
Select @MyVar;
This statement errors out for me with incorrect syntax. So, is there a way to do this in one line or am I still stuck with a Declare @MyVar, Select @MyVar =, and Select @MyVar type of setup in this particular situation?
July 21, 2011 at 8:51 am
Wrap it inside parenthesis:
declare @i int = (select count(*) from sys.columns)
works for me.
Hope this helps
-- Gianluca Sartori
July 21, 2011 at 8:52 am
Declare @MyVar INT = (Select Max(MyID) AS mID from dbo.MyTable);
July 21, 2011 at 8:53 am
... and I have this in prod since last year so I'm sure it's safe and doable ;-).
July 21, 2011 at 8:53 am
Ahha!
Thanks, guys.
July 21, 2011 at 8:55 am
Quicker than #sqlhelp!
:hehe:
-- Gianluca Sartori
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply