Returning multiple columns in a SELECT statement in a procedure 1

[DavidPike]

What is the syntax (if any) to set multiple variables from a sengle SELECT statement in a stored procedure. For instance, I might:
@WorkVar = (SELECT columnX FROM tableX WHERE condition)
and get a single variable filled. Is there a syntax for bringing back multiple columns into multiple variables with a single SELECT statement? Without using a CURSOR?

 

[SQLDenis]

SELECT @WorkVar =columnX,@WorkVar2=columnY FROM tableX WHERE condition

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[DavidPike]

SQLDenis, your the best.

 

[ESquared]

Note there is a difference between the two syntaxes:

In the first "select @var = (<query>)" the variable will always be set to a new value, null if the query returns no rows.

In the second "select @var1 = val1, @var2=val2 from blah' the two variables will be left at their values before the select statement if no rows are found that match the criteria.

So be careful.