CODEWITHSUNDEEP
sqlsql-server-2008
PriceCheaperton
PriceCheaperton

Reputation: 5349

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 ','.

Upvotes: 1

Views: 48987

Answers (4)

Tilak
Tilak

Reputation: 30698

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)

Upvotes: 5

Allan
Allan

Reputation: 1

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)

Upvotes: 0

Dezigo
Dezigo

Reputation: 3256

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;

Upvotes: 3

Vijay Hulmani
Vijay Hulmani

Reputation: 979

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.

Upvotes: 3

Related Questions