IN Operator in Dynamic SQL where Values are from Parameter

Question

I am a beginner in SQL.This may be a old post but I did not get any simplified solution for that.

My concern is how to use IN clause in dynamic SQL where the IN items are based on the parameters having multiple values.

I have a table named Employee.

I have written a dynamic SQL that will return the employee lists where Department = 'IT,System'

SQL:

Declare @Department varchar(50)
Declare @SQL nvarchar(MAX)
Declare @Parameters nvarchar(50)

Set @Department='IT,System'
SET @SQL = 'select * from Employee where Department IN(@PDepartment)'     
SET @Parameters=N'@PDepartment nvarchar(50)'

--PRINT @SQL    
EXEC sp_Executesql @SQL,@Parameters,@PDepartment=@Department

This script is not working for more than one departments and returning no result. The same query is returning result if @Department = 'IT'

Here I have mentioned a small part of the real scenario. In the actual case the table name, the column name that will include in IN operator are both dynamic and will be accepted as stored proc parameter.

Thanks for the help.

GMB · Accepted Answer · 2020-06-20 17:44:29Z

2

This query should error, since you are missing single quotes around the elements of the list.

It would also be much simpler to use a table variable as an argument rather than a CSV string.

As for dealing with the string parameter, you don't really need dynamic SQL for this. If you are running SQL Server 2016 or higher, you can use table-valued function string_split():

select e.*
from employee e
where exists (
    select 1 from string_split(@Department, ',') where value = e.department
)

In ealier versions, you can use like:

select e.*
from employee e
where ',' + @Department + ',' like '%,' + e.department + ',%'

answered Jun 20, 2020 at 17:44

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anadi Over a year ago

Thanks @GMB for the answer. Here I have mentioned a demo. This is actually needed somewhere where I can use only the dynamic SQL. There the table name, the column name that should include IN operator are dynamic

user212514 · Accepted Answer · 2020-06-20 19:54:22Z

1

I'm not very enthusiastic about this and you'd have to beware of there being apostrophes in the department names, but a simple solution may be:

Declare @Department varchar(50)
Declare @SQL nvarchar(MAX)

Set @Department='IT,System'
SET @SQL = 'select * from Employee where Department IN (''' + replace(@Department, ',', ''', ''') + ''')'

PRINT @SQL

answered Jun 20, 2020 at 19:54

user212514

3,1381 gold badge17 silver badges11 bronze badges

Collectives™ on Stack Overflow

IN Operator in Dynamic SQL where Values are from Parameter

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related