SSIS How to Use Parameters with IN Clause

Question

emailVariable = john@example.com, sally@testing.com

SQL Query:

select *
from [table_1]
where email in (?);

Parameter set to use emailVariable.

This returns nothing, both emails are valid.

Am I doing something wrong?

I am using an OLE DB Source Editor.

The same question from 2009, offering up "new" features in SQL Server 2008. [SQL : in clause in stored procedure:how to pass values](https://stackoverflow.com/questions/1525126/sql-in-clause-in-stored-procedurehow-to-pass-values) — Eric Brandt, Nov 08 '21 at 21:46

KeithL · Accepted Answer · 2021-11-10T14:22:24.587

3

You can also use string_split:

declare @stringToSplit varchar(255) = 'john@example.com, sally@testing.com'

select *
from [table_1]
where email in (
                select ltrim(rtrim(value)) from string_split(?,',')
                )

String_Split will return a table of values based on your input string and the delimiter. In your case you also need ltrim and rtrim because of extra spaces.

edited Nov 10 '21 at 14:22

answered Nov 08 '21 at 17:56

KeithL

5,348
3
19
25

score 1 · Answer 2 · answered Nov 08 '21 at 08:35

This is a classic mistake. Although the following works:

where email in ('john@example.com','sally@testing.com')

you cannot use one variable to put a multitude of values. The comma(s) is not part of the value string, it is considered code. What you can do is use dynamic sql:

declare @emailVariable nvarchar(max)=N'''john@example.com'',''sally@testing.com''' -- notice the escaped quotes

declare @sql nvarchar(max)
set @sql=N'select * from [Table_1] where email in (' + @emailVariable + ')'

exec(@sql)

SSIS How to Use Parameters with IN Clause

2 Answers2