Save Variable in SQL

Question

Is there a way for me to save the values my SELECT statement returns in to a variable in SQL?

This is what i want to do, first i want to SELECT all the columns i wanna check so in this case ItemNames of the items with ItemCategory US.

SELECT `ItemName` where `ItemCategory´= 'US'

Then I want to save the ItemName from the statement above into a variable in SQL to use it later on in the same query. Like this:

SELECT `DatabaseID` where `Project` NOT LIKE `myVariable`

Thanks in regards

see this: http://stackoverflow.com/questions/10070406/how-to-store-query-result-in-variable-using-mysql — Pathik Vejani, Feb 24 '16 at 13:30

score 7 · Answer 1 · answered Feb 24 '16 at 13:32

You can try like this:

SELECT `ItemName` into @myVar FROM mytable where `ItemCategory´= 'US';

or

SELECT @myVar := `ItemName` FROM mytable where `ItemCategory´= 'US'

However you can combine your query to one like this:

SELECT `DatabaseID` from mytable 
where `Project` NOT IN (SELECT `ItemName` where `ItemCategory´= 'US')

score 1 · Answer 2 · edited Feb 24 '16 at 13:38

1

declare @var varchar(50);
SELECT @var = 'US' 
SELECT * FROM Country where Name NOT LIKE @var

edited Feb 24 '16 at 13:38

Rahul Tripathi

168,305
31
280
331

answered Feb 24 '16 at 13:38

Abdul Waheed Khan

51
5

Danny Leandro · Answer 3 · 2016-02-24T14:12:21.600

What you can do is store the results in a variable table or temporary table, something like this:

SQL Server:

create table #Temp
(
    ItemName As Varchar(50)
    --col2 AS Int, 
    --col3 As Varchar(20)
    --...
)

MySQL

CREATE TEMPORARY TABLE IF NOT EXISTS table1 (ItemName VARCHAR(50)) -- You can add more columns: (ItemName VARCHAR(50), col2 int NOT NULL DEFAULT 0, col3 VARCHAR(20),...)

Then put the results in the table #Temp or table1

SQL Server

Insert Into #Temp SELECT ItemName FROM mytable where ItemCategory= 'US';

MySQL

Insert Into table1 SELECT ItemName FROM mytable where ItemCategory= 'US';

And then you can play with both tables and make different operations (e.g. make joins)

Save Variable in SQL

3 Answers3