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:

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

Answer 1

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.