SQL Server stored procedure parameter for IN string in WHERE clause

Question

Stored procedure ItemList has a string parameter for WHERE clause IN filter:

WHERE Item IN (@FilterStr)

It works, if the filter is single item only.

EXEC ItemList 'ABC'

How to call the stored procedure, if multiple items need to be passed for the below SQL WHERE clause?

WHERE Item IN ('ABC', 'DEF', 'HJK')

Cannot pass it by 'ABC','DEF','HJK'. Because comma will be parameter separator. Thanks.

See the answers this question https://dba.stackexchange.com/questions/629/passing-array-parameters-to-a-stored-procedure — DataWriter, Apr 07 '18 at 22:10
SQL Server does not support macro substituion. If 2016+ look at string_split() — John Cappelletti, Apr 07 '18 at 22:11
Tag you question with the version of SQL Server. See http://www.sommarskog.se/arrays-in-sql.html. — Dan Guzman, Apr 07 '18 at 22:42
Your options are dynamic SQL, [table-valued parameters](https://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql) in 2008 and above, or [`STRING_SPLIT()`](https://sqlperformance.com/2016/03/sql-server-2016/string-split) in 2016 and above. — Aaron Bertrand, Apr 07 '18 at 22:55
Where is that parameter coming from? Pure t-sql does not provide that many options - as Aaron wrote, but if it's coming from a .net client, for instance, it's possible to build the [parameters list dynamically.](https://stackoverflow.com/questions/46520435/multiple-ids-in-in-clause-of-sql-query-c-sharp/46521159#46521159) — Zohar Peled, Apr 08 '18 at 04:20
@AaronBertrand Isn't Jeff Moden's DelimitedSplit8K or Adam Machanic's CLR function good enough for most cases? — Zohar Peled, Apr 08 '18 at 07:34
@ZoharPeled In this specific case, dynamic SQL would be much less complex, and table-valued parameters would be preferable to splitting. — Aaron Bertrand, Apr 08 '18 at 13:10

score 0 · Answer 1 · answered Apr 08 '18 at 04:11

Assuming that Item is VARCHAR. You can try like following.

DECLARE @FilterStr VARCHAR(MAX)
SET @FilterStr ='''ABC'',''DEF'',''HJK'''

--Query
WHERE Item IN (
SELECT 
        Split.a.value('.', 'VARCHAR(100)') AS Item  
FROM  
    (
    SELECT CAST ('<M>' + REPLACE(@FilterStr, ',', '</M><M>') 
              + '</M>' AS XML) AS CVS 
    ) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)
)

score 0 · Answer 2 · answered Apr 08 '18 at 11:43

0

declare @parameters nvarchar(100) = '''AAA'',''BBB'',''CCC''';
declare @Query nvarchar(100) = 'select * from [WFL].[TBL_WFTBaseInfo] where 
[wbiEntity] in ('+@parameters+' )';
exec(@query)

answered Apr 08 '18 at 11:43

Hadi Ardebili

99
1
7

1

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. – Maximilian Peters Apr 08 '18 at 12:14

SQL Server stored procedure parameter for IN string in WHERE clause

2 Answers2