Declare multiple value variable in SQL

问题:

I would like to write an IN statement when setting the RECEIPTIDS variable, so that I can pass multiple values in that format into my query. I have tried the following:

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

However, I get the error:

Incorrect syntax near ','.

回答1:

You need extra 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.

回答2:

Use temporary array or temporary List

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

回答3:

You need table variable 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)

回答4:

Didn't work for me either but this did:

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

where field IN (Select val from @RECEIPTIDS)