Reuse values from a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Reuse values from a query

Author

Topic

jeffnc
Starting Member

14 Posts

Posted - 2008-07-15 : 09:01:38

In the following code, I'm trying to avoid making multiple queries, when the data should already be available. How can I reuse the results from the first query? Instead of SELECT *, I could write SELECT AA, BB, CC. But I don't understand the sytax I'd use for plugging in those values rather than getting the value from another SELECT statement.

The #IF is some customized preprocessing that I included just to give some context for this problem. It would not of course show up in the final SQL. If I didn't include the context this code sample would probably not make any sense :-)

DECLARE @a SMALLINT
DECLARE @b SMALLINT
DECLARE @c SMALLINT

SET @a = 1
SET @b = 2
SET @c = 3

IF EXISTS(SELECT * FROM Table1 WHERE [ID] = '1234')
BEGIN

#IF xxxx
SET @a = (SELECT AA FROM Table1 WHERE [ID] = '1234')
SET @b = (SELECT BB FROM Table1 WHERE [ID] = '1234')
SET @c = (SELECT CC FROM Table1 WHERE [ID] = '1234')
#ENDIF

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 09:04:54

Instead of

#IF xxxx
SET @a = (SELECT AA FROM Table1 WHERE [ID] = '1234')
SET @b = (SELECT BB FROM Table1 WHERE [ID] = '1234')
SET @c = (SELECT CC FROM Table1 WHERE [ID] = '1234')
#ENDIF

use

#IF xxxx
SELECT @a =AA, @b=BB,@c=CC FROM Table1 WHERE [ID] = '1234'
#ENDIF

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 09:05:25

[code]SELECT @a = MAX(CASE WHEN ID = '1234' THEN AA ELSE NULL END),
@b = MAX(CASE WHEN ID = '1234' THEN BB ELSE NULL END),
@c = MAX(CASE WHEN ID = '1234' THEN CC ELSE NULL END)
FROM Table1[/code]

E 12°55'05.25"
N 56°04'39.16"

jeffnc
Starting Member

14 Posts

Posted - 2008-07-15 : 09:19:34

So it sounds like there isn't really a way to "reuse" the results from the first SELECT * query. I didn't know if you could. But the alternative you guys showed is more efficient for the followup query, thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 09:23:04

quote:
Originally posted by jeffnc

So it sounds like there isn't really a way to "reuse" the results from the first SELECT * query. I didn't know if you could. But the alternative you guys showed is more efficient for the followup query, thanks.

As you used the SELECT statement in IF EXISTS.., you cant reuse the result

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 09:30:40

[code]SELECT @a = COALESCE(MAX(CASE WHEN ID = '1234' THEN AA ELSE NULL END), 1),
@b = COALESCE(MAX(CASE WHEN ID = '1234' THEN BB ELSE NULL END), 2),
@c = COALESCE(MAX(CASE WHEN ID = '1234' THEN CC ELSE NULL END), 3)
FROM Table1[/code]

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2008-07-15 : 09:42:38

SELECT * INTO #ReUseTable FROM Table1 WHERE [ID] = '1234'

SELECT * FROM #ReUseTable

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources