Btukfyl

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

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.

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;

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

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.