How to pass comma seperated values in IN where clause

Question

I want to ask how to pass the '30','31','32' to SQL WHERE IN clause For example

EXEC SQL DECLARE abcd CURSOR FOR SELECT Consignment FROM Transaction WHERE order_type IN (:dest)

where dest will hold the value '30','31','32'.

I have tried as above example but it is not working SQL did not return any result.

Answer 1

There is no easy way.split the data on seperator(,), insert into a table in a while loop. Then "IN (select "column_name" from temp table)". Now split and insert into a temp table is a tricky thing.

DECLARE @StringToSeperate VARCHAR(10)
SELECT @StringToSeperate = '1,2,5'

--SELECT @StringToSeperate IDs INTO #Test


CREATE TABLE #valus (ID int) 

DECLARE @CommaSeperatedValue VARCHAR(255) = ''
DECLARE @Position INT = LEN(@StringToSeperate)

--Add Each Value
WHILE CHARINDEX(',', @StringToSeperate) > 0
BEGIN
    SELECT @Position  = CHARINDEX(',', @StringToSeperate)  
    SELECT @CommaSeperatedValue = SUBSTRING(@StringToSeperate, 1, @Position-1)

    INSERT INTO #valus 
    SELECT @CommaSeperatedValue

    SELECT @StringToSeperate = SUBSTRING(@StringToSeperate, @Position+1, LEN(@StringToSeperate)-@Position)

END

--Add Last Value
IF (LEN(LTRIM(RTRIM(@StringToSeperate)))>0)
BEGIN
    INSERT INTO #valus
    SELECT SUBSTRING(@StringToSeperate, 1, @Position)
END

SELECT ID FROM #valus