CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Bob Horn
Bob Horn

Reputation: 34297

Select and assign to variable in one statement?

I'd like to display the results of a query, and I'd like to capture a column's value at the same time. The FROM and WHERE are the same in both queries. Can I do both in one query, or is it easier/better to just do the query twice, like this?

DECLARE @fooId INT

SET @fooId = (SELECT FooId FROM Bar WHERE SnuhId = 5)

SELECT * FROM Bar WHERE SnuhId = 5

Upvotes: 6

Views: 9024

Answers (3)

Rafał Czabaj
Rafał Czabaj

Reputation: 740

You can do the following to reduce one line:

DECLARE @fooI INT
SET @fooId = (SELECT FooId FROM Bar WHERE SnuhId = 5)
SELECT @fooId as [FooId]

It will reduce unnecessary replication of your query.

I hope this helps.

Upvotes: 2

Arjit
Arjit

Reputation: 3456

If you try to do it in one query, you will get an error msg - A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

To avoid this, the possibilities are

1) Make sure that all columns are assigned to a local variable. Ex

DECLARE @fooId INT
DECLARE @barId INT

SELECT @fooId = FooId, @barId = BarId FROM Bar WHERE SnuhId = 5

2) simply remove '*' from the SELECT statement

SELECT @fooId = FooId, BarId FROM Bar WHERE SnuhId = 5 //remove BarId column from this query

3) If you really need to do both, meaning to assign the value to local variables and to return the columns as a result set, you have to do it in 2 steps instead of combining them into one SELECT statement.

Upvotes: 3

Dave C
Dave C

Reputation: 7392

Unfortunately it has to be done in two operations.

Test:

DECLARE @VAR DATETIME
SELECT @VAR=GETDATE(), GETDATE()

Yields error message 141.

Here's another SO post on this.

Upvotes: 6

Related Questions