How do I DECLARE or SET multiple values in SQL?-CodePudding

Goal: Set multiple values in the DECLARE statement to use in the WHERE statement.

Issue: Cannot successfully add multiple values in the SET statement.

Example of what I’m trying to complete:

DECLARE @Beverage as varchar(1000)
SET @Beverage = (‘Water’, ‘Soda’, ‘Wine’, ‘Beer’)

SELECT Beverage
FROM ExampleServer
WHERE Beverage in (@Beverage)

Example of what currently executes correctly:

DECLARE @Beverage as varchar(1000)
SET @Beverage = (‘Water’)

SELECT Beverage
FROM ExampleServer
WHERE Beverage in (@Beverage)

CodePudding user response：

declare a variable as a temporary table :

declare @myList table (Id int)

populate it with values :

insert into @myList values (1), (2), (5), (7), (10)

Then you can use either the in statement :

select * from DBTable
where id in (select Id from @myList)

CodePudding user response：

A variable can hold a single scalar value, you can't assign multiple values.

If you wish to hard-code a list of values like in your question you can use a values table constructor; you can then select from your table and check with an exists correlation:

with b as (
  select beverage from (values ('Water'), ('Soda'), ('Wine'), ('Beer'))b(beverage)
)
select t.Beverage
from ExampleServer t
where exists (
  select * from b
  where b.beverage = t.beverage
);

The above is assuming SQL Server

CodePudding user response：

You can use the " EXEC (@sqlString) ". This will help you to build your SQL queries dynamically and then execute them with the command EXEC as if they are Stored procedure

DECLARE @BEVERAGE VARCHAR(200),@SQLstring VARCHAR(1000)

SET @BEVERAGE ='(' CHAR(39) 'Water' CHAR(39) ',' CHAR(39) 'Soda' CHAR(39) 
              ',' CHAR(39) 'Wine' CHAR(39) ',' CHAR(39) 'Beer' CHAR(39) ')'
SET @SQLstring = 'SELECT Beverage FROM ExampleServer WHERE Beverage IN 
                 ' @BEVERAGE

--SELECT @SQLstring
EXEC (@SQLstring)

I'm using the "CHAR(39)" instead of " ' " for avoid some troubles with the string declaration