Pass delimited string to stored procedure

Transact-SQL
1

I am trying to pass a string of employee numbers to a stored procedure and can't get it right. I have seen the use of this:

Where CHARINDEX( ',' + Emps.repno + ',', ',' + @RepList + ',' ) > 0

but can't get it to work like I need. I need the list of employees numbers to be used in a NOT IN clause instead, like

Where Emps.repno NOT IN ('ABC', 'DEF')

I am passing 'ABC', 'DEF' to the stored procedure and @RepList is the parameter name where I am passing the string of 'ABC', 'DEF'

TIA
Bill

2

couple of choices I guess:

  1. use dynamic SQL to construct the query:

    select ...
    from ...
    where Emps.repno not in (' + @RepList + ')

  2. use a splitter function like Moden's:

Splitter

3

Thanks for the reply. I had considered the dynamic SQL that but in this case it is a little more difficult though not out of the question. I looked into the Splitter and will look at it again. Still not really the way I prefer to solve this however. Anyone have nay other suggestions?

TIA
Bill

4

You can try the following. Logically it would work, but it is likely to have poor performance because of the inability to use any index you may have to evaluate the logical expression.

WHERE
	','+@RepList+',' NOT LIKE '%,'+Emps.repno+',%'

My recommendation would be to use the string splitter method that @gbritton pointed to.

5

James:
Thanks for your reply. I tried your suggestion but it did not work. To test this first, before jumping thru the hoops of getting all of this into production, I created a variable called @RepList and populated like this:

Set @RepList = '''ABC'',''XYZ'''
All of those are single ticks.

When I do just a SELECT @RepList I get 'ABC','XYZ'

When I run my query using:
WHERE ','+@RepList+',' NOT LIKE '%,'+Emps.repno+',%'

ABC and XYZ are NOT excluded, they do appear in the output. And, I realize that the suggestions made to use the string splitter are being preferred but I would like to tackle it this way. So, if there are any other ideas then please post them. Thanks

6

Don't add the single quotes.

SET @RepList = 'ABC,XYZ';

Or change the WHERE clause to

WHERE
	','+REPLACE(@RepList, '''','')+',' NOT LIKE '%,'+Emps.repno+',%'
7

So what way would you prefer? No sense in us guessing. Tell us you're preferred method.

8

Excellent. That did the job. Thanks