SQL query, store result of SELECT in local variable

Question

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:

to stop selecting the result so many times - in my sample, I reselected the table 3 times
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

Vince Pergolizzi · Accepted Answer · 2012-04-27 18:11:06Z

115

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.

answered Apr 27, 2012 at 18:11

Vince Pergolizzi

6,6223 gold badges22 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anthony Faull · Accepted Answer · 2012-04-27 19:24:06Z

26

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;

answered Apr 27, 2012 at 19:24

Anthony Faull

18.1k5 gold badges58 silver badges75 bronze badges

2 Comments

forX Over a year ago

with cte seems interesting. dont need to create table of each row. tank you

amiry jd Over a year ago

@AnthonyFaull thanks to answer. But, how about an in(statement) statement?

Nico van Niekerk · Accepted Answer · 2016-04-26 16:50:01Z

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

G. Gordon Nasseri · Accepted Answer · 2018-11-27 20:50:36Z

7

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.

answered Nov 27, 2018 at 20:50

G. Gordon Nasseri

1071 silver badge4 bronze badges

1 Comment

Caltor Over a year ago

You can also do that with select @myVal = a from table1 - see learn.microsoft.com/en-us/sql/t-sql/language-elements/…

Collectives™ on Stack Overflow

SQL query, store result of SELECT in local variable

4 Answers 4

Comments

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related