Friday, March 9, 2012

How to SET multiple variables from one table record?

It's come up more than once for me, where I need to DECLARE and SET several SQL variables in a Stored Procedure where many of these values come from the same table record - what is the best method for doing this, where I don't have to resort to making a separate query for each value.

Currently I'll do something like this:

DECLARE @.var1 int
SET @.var1 = (SELECT TOP 1 field1 FROM table1 WHERE recordkey = @.somekey)
DECLARE @.var2 nvarchar(20)
SET @.var2 = (SELECT TOP 1 field2 FROM table1 WHERE recordkey = @.somekey)

Of course, I'd rather just have to query "table1" just once to assign my variables.

What obvious bit of T-SQL am I missing?

Thank you in advance.

Select @.var1 = field1, @.var2 = field2, @.var3 = field3 from table1 where recordid = @.recid

|||

Thank you PDraigh!

I knew it was something obvious - I think I need a holidayEmbarrassed [:$]

No comments:

Post a Comment