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 ','.
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)
Must declare the scalar variable
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.
single value whereas OP asked about multiple values in one variable, you would need to include a use of a function to split that string into a sequence of values to provide a complete example
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;
Must declare the scalar variableDidn'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)
