Geek Answers Handbook

Declare multiple value variables in SQL

I would like to write an IN statement when setting a variable RECEIPTIDSso that I can pass multiple values ​​in this format into my query. I tried the following:

DECLARE @RECEIPTIDS VARCHAR(2000)
SET @RECEIPTIDS = ('R00013','R00028')

However, I get the error message:

Invalid syntax next to ','.

+1
source share
4 answers

You need additional single qoutes.

    create table MyTable
    (
       ID varchar(50) 
    )
    insert into MyTable values('R00013')
    insert into MyTable values('R00028')
    insert into MyTable values('R00015')

    DECLARE @RECEIPTIDS VARCHAR(2000)
    SET @RECEIPTIDS = ('''R00013'',''R00028''')
    DECLARE @QUERY VARCHAR(100)

    SET @QUERY='SELECT * 
    from MyTable 
    where ID IN ('+@RECEIPTIDS+')'
    EXEC (@QUERY)

Edited: use it with dynamic query.

+3
source

Do you need or temp table .

DECLARE @RECEIPTIDS TABLE(val VARCHAR(100))
Insert into @RECEIPTIDS values ('R00013'), ('R00028')

You can use it in IN as

where field IN (Select val from @RECEIPTIDS)
+3
source 

DECLARE @ListofIDs TABLE(IDs VARCHAR(100))
INSERT INTO @ListofIDs VALUES('a'),('10'),('20'),('c'),('30'),('d')
SELECT IDs FROM @ListofIDs;
+2

, :

DECLARE @RECEIPTIDS TABLE(val VARCHAR(100))
Insert into @RECEIPTIDS values ('R00013'), ('R00028')

where field IN (Select val from @RECEIPTIDS)
0

More articles:


All Articles