SQL query, store result of SELECT in local variable

how to store select query result in variable in sql server
how to store select query result in variable in mysql
how to store dynamic query result in variable in sql
assign value to variable in sql select statement
using variables in sql select statement
sql set variable from select
sql set multiple variables from select
set local variable in select case statement

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.

SELECT @local_variable (Transact-SQL), Sets a local variable to the value of an expression. contains a variable assignment cannot be used to also perform typical result set retrieval operations. The query against the Store table returns no rows because the value  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.

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;

Store the resulting list of a SELECT in a variable to count AND use , If you want to put a query result in a table variable and iterate through the rows that can be easily done. Be careful with your loops of course. DECLARE @Value​  SQL. -- Uses AdventureWorks DECLARE @var1 varchar(30) SELECT @var1 = 'Generic Name' SELECT @var1 = (SELECT Name FROM Sales.Store WHERE CustomerID = 1000) SELECT @var1 AS 'Company Name' ; Here is the result set. Company Name ---------------------------- NULL.

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

This article explores the SQL variables using SET and Select SQL , What to choose when assigning values to SQL Server variables: SET vs SELECT T-SQL statements T-SQL to assign a value to a previously created local SQL variable. The results of the previous two queries will be displayed in the export in SQL Server · SQL Server stored procedures for beginners. I am new to jython script. I am trying to store the result of the SELECT query into a variable. The select query output is just "2020". I am trying to store in a variable called YEAR, but the below

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.

MySQL SELECT INTO Variable, To store query result in one or more variables, you use the SELECT INTO variable syntax: SELECT c1, c2, c3, INTO @v1, @v2, @v3, The other day during Comprehensive Database Performance Health Check, I was asked if there is a way to store results of dynamic SQL into a variable. This is a very popular question and let us see the answer to this question in this blog post.

Using SQL Variables in Queries - MySQL Cookbook [Book], How do you store results of select query in a variable? Types of Variable: Local, Global. MS SQL has two types of variables: Local variable ; Global variable. However, the user can only create a local variable. Below figure explain two types of variable available in MS SQL server. Type of Variables in SQL Server Local variable: A user declares the local variable. By default, a local variable starts with @.

PL/SQL SELECT INTO Statement By Practice Examples, How do you assign query result to a variable in SQL? So far what I do is concatenate the entire SQL code with the variables to use. Like this: declare @table_name as varchar(30) select @table_name = CONVERT(varchar(30), getdate(), 112) set @table_name = 'DAILY_SNAPSHOT_' + @table_name EXEC(' SELECT var1, var2, var3 INTO '+@table_name+' FROM my_view WHERE string = ''Strings must use double apostrophe'' ');

Variables in SQL Server Stored Procedures, wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement. In SQL Server, local variables are used to store data during the batch execution period. The local variables can be created for different data types and can also be assigned values. Additionally, variable assigned values can be changed during the execution period.

Comments
  • yeah, that was what I think I should do. I just post it to know if theres another way. Tank you.
  • with cte seems interesting. dont need to create table of each row. tank you
  • @AnthonyFaull thanks to answer. But, how about an in(statement) statement?