SSIS How to Use Parameters with IN Clause-CodePudding

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.

CodePudding user response：

You can also use string_split:

declare @stringToSplit varchar(255) = '[email protected], [email protected]'

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 casse you also need ltrim and rtrim because of extra spaces.

CodePudding user response：

This is a classic mistake. Although the following works:

where email in ('[email protected]','[email protected]')

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'''[email protected]'',''[email protected]''' -- notice the escaped quotes

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

exec(@sql)