August 31, 2010 at 2:09 pm
Hello Everyone
Is there a way to assign values to multiple variables with one query?
for eaxmple, can this be accomplished :
DECLARE @FirstName varchar(25)
DECLARE @MiddleName varchar(25)
DECLARE @LastName varchar(25)
SET @FirstName, @MiddleName, @LastName =
(
SELECT
FirstName, MiddleName, LastName
FROM
SomeTable
WHERE SomeColumn = SomeValue
)
The only way that I know is one at a time. One query to one variable
Thanks in advance
Andrew SQLDBA
August 31, 2010 at 2:20 pm
SELECT TOP 1
@FirstName = FirstName,
@MiddleName = MiddleName,
@LastName = LastName
FROM
SomeTable
WHERE SomeColumn = SomeValue
ORDER BY SomeOtherColumn
Please note that I added TOP 1 together with an ORDER BY to get a deterministic result. Otherwise you'd get the "last selected value", which is pretty much random.
August 31, 2010 at 2:50 pm
Thanks Lutz
That works perfectly. I have always used SET, which will allow only one value, SELECT allows multiple values.
Another fundamental difference between SET and SELECT is that, you can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time. Here's how:
/* Declaring variables */
DECLARE @Variable1 AS int, @Variable2 AS int
/* Initializing two variables at once */
SELECT @Variable1 = 1, @Variable2 = 2
/* The same can be done using SET, but two SET statements are needed */
SET @Variable1 = 1
SET @Variable2 = 2
Andrew SQLDBA
August 31, 2010 at 5:45 pm
One thing you may not know is that you can reference variables assigned previously in the select statement:
declare @MonthStart datetime, @MonthEnd datetime;
select @MonthStart = dateadd(month, DateDiff(month, 0, GetDate()), 0),
@MonthEnd = dateadd(ms, -3, dateadd(month, 1, @MonthStart));
select @MonthStart, @MonthEnd;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply