SQL query, store result of SELECT in local variable
sql-server-2005
Solution
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.
Problem
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