Assigning multiple variables at once ...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Assigning multiple variables at once ...

Author

Topic

wgpubs
Yak Posting Veteran

67 Posts

Posted - 2004-05-06 : 14:50:47

Is there any way to accomplish something like below:

SELECT @Subject=SubjectToUse, @Body=BodyToUse FROM
(SELECT 'SubjectToUse' =
CASE e.Subject
WHEN NULL THEN et.DefaultSubject
ELSE e.Subject
END,
'BodyToUse' =
CASE e.Body
WHEN NULL THEN et.DefaultBody
ELSE e.Body
END
FROM Table2 et
LEFT JOIN Table1 e ON (et.Col1 = e.Col1)
WHERE et.Col1 = 1)

this ain't working.

thanks - wg

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-06 : 14:59:12

SELECT
@Subject = a.SubjectToUse,
@Body = a.BodyToUse
FROM (
SELECT
CASE
WHEN e.Subject IS NULL THEN et.DefaultSubject
ELSE e.Subject
END AS SubjectToUse,
CASE
WHEN e.Body IS NULL THEN et.DefaultBody
ELSE e.Body
END AS BodyToUse
FROM
Table2 et
LEFT JOIN Table1 e ON (et.Col1 = e.Col1)
WHERE
et.Col1 = 1) a

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-05-06 : 15:00:08

hi!

maybe like this:

SELECT @Subject =
CASE e.Subject
WHEN NULL THEN et.DefaultSubject
ELSE e.Subject
END,
@Body =
CASE e.Body
WHEN NULL THEN et.DefaultBody
ELSE e.Body
END
FROM Table2 et
LEFT JOIN Table1 e ON (et.Col1 = e.Col1)
WHERE et.Col1 = 1

but this will set the values of variables to the last value in the resulted query.

what exactly are you trying to do?

Go with the flow & have fun! Else fight the flow :)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-06 : 15:08:18

I should have read this post before I said anything. :) You can only assign one value to a variable at a time. If you want to do assign all the values from the select, you will need to do it with a loop. Load all the values into a temp table with an identity column, then use a WHILE loop to assign the values to the variable one at a time. You can the do what you need to with the variables on each iteration.

It would be better to just load all the values into a temp table, then join to the temp table and do whatever else you need to do in sets though. This is really how SQL is designed to work and is most efficient.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources