How to program the in clause in tsql query

Question

I have the following code

declare @code nvarchar
set @code='2/2017,3/2017'

select *
from payroll
where id_code in (@code)

But I am getting an error in the where clause.

What you want to acheive exactly? and what is the error you get? — Ilyes, Sep 18 '17 at 12:38
Possible duplicate of [Define variable to use with IN operator (T-SQL)](https://stackoverflow.com/questions/1707326/define-variable-to-use-with-in-operator-t-sql) — user5226582, Sep 18 '17 at 12:41
@TechGirl this will not work. first of all the type is `nvarchar` not a list, second it would have been single quotes — user5226582, Sep 18 '17 at 12:43
Are you using SQL Server or Oracle? That code wont run on both... — jarlh, Sep 18 '17 at 12:43
yes, also you will need to have dynamic query if string needs to be passed in where IN — TechGirl, Sep 18 '17 at 12:44
thank you techgril ,yes i want dynamic query in the IN sql part — larry harry, Sep 18 '17 at 12:47
This is not Oracle related at all . Please remove the Oracle tag. — BriteSponge, Sep 18 '17 at 13:00
Can you use a table valued parameter instead of delimited string? If not you either need to use dynamic sql or a string splitter. — Sean Lange, Sep 18 '17 at 13:11

Victor Hugo Terceros · Accepted Answer · 2017-09-19T13:49:03.917

3

If you are using SQL Server 2016 you can use STRING_SPLIT

declare @code nvarchar
set @code='2/2017,3/2017'

select *
from payroll
where id_code in (SELECT value FROM STRING_SPLIT(@code,','))

for older versions of SQL you can use this as a guide T-SQL split string based on delimiter

edited Sep 19 '17 at 13:49

answered Sep 18 '17 at 14:13

1

Actually, I think the last bit should be `IN (SELECT value FROM STRING_SPLIT(@code, ','))`. – RBarryYoung Sep 19 '17 at 13:46

1 Answers1