Its looking like this isn't possible - but is there a way to set multiple
variables to specific values returned from a single select?

ie
Declare @A as VarChar(50)
Declare @B as VarChar(50)
Declare @C as VarChar(50)

Set @A, @B, @C = (SELECT ColA, ColB, ColC FROM TblName Where PriKey = 500)

OR, would I need to have separate SELECTs per variable?

Set @A = (Select ColA from TblName Where PriKey = 500)
Set @B = (Select ColB from TblName Where PriKey = 500)
Set @C = (Select ColC from TblName Where PriKey = 500)

OR, is there a better way entirely?

SELECT @A = ColA,
       @B = ColB,
       @C = ColC
FROM TblName
WHERE PriKey = 500

HTH,
Stu

Just to expand a bit on Stu's answer:

If SQL Server allowed "row valued constructors" (in ANSI SQL), you could do:

Set (@A, @B, @C) = (SELECT ColA, ColB, ColC FROM TblName Where PriKey = 500)

Just as you can do today, both with only one column.
If the SELECT return 0 rows, the variables would have NULL
If the SELECT returns > 1 rows, you get an error.

The SQL Server way (which Stu posted) work differently:
If the SELECT return 0 rows, the variables are untouched
If the SELECT returns > 1 rows, a assignment will be done, but you cannot control from which of the
rows returned.

As you can see, without knowledge of this, you can get pretty creepy bugs in your code (if several
rows are returned). Make sure you check @@ROCOUNT if you cannot be 100% that there are only 1 rows
returned and the initial variable value is NULL.

Show quoteHide quote

Hey Tibor,

I would have warned him about the >1 issue, but since he was selecting
on his primary key, I thought it would be safe :)

Stu

Table scan on 0 record temp table?
Parsing a TEXT column in an SP or trigger (SQL SERVER 2000)
Print statements not displaying
Can I Simplify and Optimize This Long Query?
CURSOR question