How to use IN operator in SQL for a dynamically generated string?

Question

Below mentioned is the sample code under discussion:

DECLARE @FROMDATE AS DATETIME
DECLARE @TODATE AS DATETIME
DECLARE @DAYS AS VARCHAR(100)

SET @FROMDATE = CONVERT(DATETIME, '2017-09-03')
SET @TODATE = CONVERT(DATETIME, '2017-09-06')

SET @DAYS = '''SUNDAY'',''MONDAY'',''TUESDAY'',''WEDNESDAY'',''FRIDAY'',''SATURDAY'''

PRINT @DAYS 
--(O/P - 'SUNDAY','MONDAY','TUESDAY','WEDNESDAY','FRIDAY','SATURDAY')

SELECT 
    UPPER(DATENAME(dw, CONVERT(DATETIME, CONVERT(VARCHAR(10), APPDATE, 112)))), 
    * 
FROM 
    SQLTABLE
WHERE 
    UPPER(DATENAME(dw, CONVERT(DATETIME, CONVERT(VARCHAR(10), APPDATE, 112)))) IN (@DAYS)

If I pass values manually as below I get output:

UPPER(DATENAME(dw, CONVERT(DATETIME, CONVERT(VARCHAR(10), APPDATE, 112)))) 
IN ('SUNDAY','MONDAY','TUESDAY','WEDNESDAY','FRIDAY','SATURDAY')

If I pass values using variable having the same values as shown in the O/P of PRINT @DAYS the result is blank:

UPPER(DATENAME(dw, CONVERT(DATETIME, CONVERT(VARCHAR(10), APPDATE, 112)))) 
IN (@DAYS)

Assuming that in actual scenario I am generating the values under @DAYS variable dynamically or passing it through code to the query / stored procedure below is my question :

Is it not allowed in SQL or am I doing it wrongly?

score 1 · Accepted Answer · answered Sep 03 '17 at 08:59

1

You need to execute it as dynamic sql:

Set @sql='SELECT ... from ...  WHERE ... NOT IN ('+@days+')'

exec sp_executesql @sql

If not, the whole param value will be considered as one value.

answered Sep 03 '17 at 08:59

sagi

40,026
6
59
84

Worked for me. Thanks. – Gaurav Mody Sep 03 '17 at 10:01

score 1 · Answer 2 · answered Sep 03 '17 at 09:33

The in operator is expecting a list of values separated by commas, but what you are providing it is a single value that contains a comma separated list.

You can solve it using dynamic SQL, as sagi suggested in his answer, or use a string split function (in SQL Server 2016 it comes built in) to convert the single comma separated variable to a table -

SET @DAYS = 'SUNDAY,MONDAY,TUESDAY,WEDNESDAY,FRIDAY,SATURDAY'

SELECT UPPER(DATENAME(dw, CONVERT(DATETIME, CONVERT(VARCHAR(10), APPDATE, 112)))), * 
FROM SQLTABLE
WHERE UPPER(DATENAME(dw, CONVERT(DATETIME, CONVERT(VARCHAR(10), APPDATE, 112)))) IN (STRING_SPLIT(@DAYS, ','))

If you are using a lower version then 2016, you need to create the string split user defined function yourself - or choose one from Aaron Bertrand's Split strings the right way – or the next best way

However, if your client supports it, I would suggest sending a table valued parameter to the database instead of dealing with comma delimited strings at all.

adiga · Answer 3 · 2017-09-03T09:53:31.957

If you're not a fan of dynamic queries, this is an alternate approach to sagi's answer. You can create a table and populate the days you want:

CREATE TABLE #Days (NAME VARCHAR(20))

INSERT INTO #Days
VALUES ('SUNDAY'),('MONDAY'),('TUESDAY'),('WEDNESDAY'),('FRIDAY'),('SATURDAY')

and then inner join it with your table:

SELECT UPPER(DATENAME(dw, CONVERT(DATETIME, CONVERT(VARCHAR(10), APPDATE, 112)))),*
FROM SQLTABLE s
INNER JOIN #Days d ON d.NAME 
          = UPPER(DATENAME(dw, CONVERT(DATETIME, CONVERT(VARCHAR(10), s.APPDATE, 112))))

If you're using SQL Server 2016+, There is a STRING_SPLIT function which returns a table with a value column.

SET @DAYS = 'SUNDAY,MONDAY,TUESDAY,WEDNESDAY,FRIDAY,SATURDAY'

SELECT UPPER(DATENAME(dw, CONVERT(DATETIME, CONVERT(VARCHAR(10), APPDATE, 112)))), *
FROM SQLTABLE s
INNER JOIN STRING_SPLIT(@DAYS, ',') d ON d.value 
        = UPPER(DATENAME(dw, CONVERT(DATETIME, CONVERT(VARCHAR(10), s.APPDATE, 112))))

(PS: Zohar Peled's implementation of STRING_SPLIT is better if you're using SQL Server 2016+)

How to use IN operator in SQL for a dynamically generated string?

3 Answers3