I would like to update several variables with a single select statement, but do not know if this is possible. I can only figure two ways to do this and I don't like either solutions.

Solution 1: Use multiple Set (or Select) statements:

Set @Var1 = (Select Col1 from...... where....)
Set @Var2 = (Select Col2 from...... where....)

The reason I do not like this solution (aside form the extra typing) is that the same select statement is issued over and over again only changing the returned value and the rest join clause, where clause etc. stays the same. It seems innefficent to me.


Solution 2: Use a cursor

Declare tmpcursor Cursor for
Select Col1, Col2, .... from...... where....)
Open tmpcursor
fetch next form tmpcursor into @Var1, @Var2, ...
close tmpcursor
deallocate tmpcursor

The reason I do not like this solution is that a cursor is created but the only reason for the cursor is so I can use the Select...Into clause. The cursor is not really used and I don't think this is too efficient either.

Note: In my particular case I only expect a single record to be returned from the above select statements.

Any comments would be appreciated.

Probably you don't have to think about the second solution at all. That is similar to taking the longest route to go to your destination.

I can modify you first solution to help you out.

You dont have to use separate SELECTs with same JOIN and WHERE for assigning values to each of the variables.

Does that helps?
Cheers!

_________________________
-Vijay G
Strive for Perfection

Thanks for the help. It works great.

Normally I prefer to use Set when assigning to local variables, but in this case Select makes things much easier!