CODEWITHSUNDEEP
sql-server-2005
forX
forX

Reputation: 2153

SQL query, store result of SELECT in local variable

I create a query with some results reused. I search a way to put the result into a variable and use it.

A simple way to see what I want something looking like this - I want this:

DECLARE @result1 ?????

SET @result1 = SELECT a,b,c FROM table1
SELECT a AS val FROM @result1
UNION
SELECT b AS val FROM @result1
UNION
SELECT c AS val FROM @result1

Not this :

 SELECT a AS val FROM (SELECT a,b,c FROM table1)
 UNION
 SELECT b AS val FROM (SELECT a,b,c FROM table1)
 UNION
 SELECT c AS val FROM (SELECT a,b,c FROM table1)

It's not the result of this query that I'm concerned with, but instead:

  1. to stop selecting the result so many times - in my sample, I reselected the table 3 times

  2. the query of @result1 is usually so much more complex. So, with a variable, the code will be cleaner.

Maybe I want to much - or there's a type of local variable. Or using the type table and set data inside.

What do you suggest me?

Thank you

Upvotes: 92

Views: 291385

Answers (4)

G. Gordon Nasseri
G. Gordon Nasseri

Reputation: 107

I came here with a similar question/problem, but I only needed a single value to be stored from the query, not an array/table of results as in the orig post. I was able to use the table method above for a single value, however I have stumbled upon an easier way to store a single value.

declare @myVal int; set @myVal = isnull((select a from table1), 0);

Make sure to default the value in the isnull statement to a valid type for your variable, in my example the value in table1 that we're storing is an int.

Upvotes: 7

Nico van Niekerk
Nico van Niekerk

Reputation: 111

Isn't this a much simpler solution, if I correctly understand the question, of course.

I want to load email addresses that are in a table called "spam" into a variable. 

select email from spam

produces the following list, say:

.accountant
.bid
.buiilldanything.com
.club
.cn
.cricket
.date
.download
.eu

To load into the variable @list:

declare @list as varchar(8000)
set @list += @list (select email from spam)

@list may now be INSERTed into a table, etc.

I hope this helps.

To use it for a .csv file or in VB, spike the code:

declare @list as varchar(8000)
set @list += @list (select '"'+email+',"' from spam)
print @list

and it produces ready-made code to use elsewhere:

".accountant,"
".bid,"
".buiilldanything.com,"
".club,"
".cn,"
".cricket,"
".date,"
".download,"
".eu,"

One can be very creative.

Thanks

Nico

Upvotes: 11

Anthony Faull
Anthony Faull

Reputation: 17957

Here are some other approaches you can take.

1. CTE with union:

;WITH cte AS (SELECT a, b, c FROM table1)
SELECT a AS val FROM cte
UNION SELECT b AS val FROM cte
UNION SELECT c AS val FROM cte;

2. CTE with unpivot:

;WITH cte AS (SELECT a, b, c FROM table1)
SELECT DISTINCT val
FROM cte
UNPIVOT (val FOR col IN (a, b, c)) u;

Upvotes: 26

Vince Pergolizzi
Vince Pergolizzi

Reputation: 6584

You can create table variables:

DECLARE @result1 TABLE (a INT, b INT, c INT)

INSERT INTO @result1
SELECT a, b, c
FROM table1

SELECT a AS val FROM @result1
UNION
SELECT b AS val FROM @result1
UNION
SELECT c AS val FROM @result1

This should be fine for what you need.

Upvotes: 114

Related Questions