Stored Procedure parameter at WHERE IN clause

Question

I have a stored procedure and it looks something like this:

SELECT * FROM empTable
WHERE empCode IN (@employeeCode)

The @employeeCode will accept an input from another application where I do NOT have the control of that application. It will give me a string something like this: 'M06', 'R02', 'B19', 'C10'

When I execute the procedure, it returns me 0 row. However, when I run the query manually in the SQL Server using the following query:

SELECT * FROM empTable
WHERE empCode IN ('M06', 'R02', 'B19', 'C10')

It returns me 15 rows.

I'm suspecting that when the employeeCode accepting the input, it assumes the entire 'M06', 'R02', 'B19', 'C10' as ONE string. Can anybody confirm this? If this is true, how am I going to fix it?

Edited: I'm using SQL Server 2000

there are a bunch of split functions easily searchable on this site. — Daniel E., Aug 19 '14 at 13:42
like this one: http://stackoverflow.com/questions/20465136/split-sql-parameter-on-comma — Daniel E., Aug 19 '14 at 13:43
The splitters on this site almost all use xml, a recursive cte or a while loop. These all work but are not very efficient. If you want a fast splitter take a look at this article as SSC. http://www.sqlservercentral.com/articles/Tally+Table/72993/ — Sean Lange, Aug 19 '14 at 13:43
You seem to be surprised that SQL Server treats a single string parameter as... a single string. It doesn't inspect the contents of the string, spot the commas (and possible additional quote characters) and decide to magically treat it as multiple parameters. Can I ask a question - have you encountered a programming language where that **does** happen? If so, how do you get it to accept a parameter where the actual data you're looking for contains comma characters? — Damien_The_Unbeliever, Aug 19 '14 at 13:48
How are you calling the procedure? Can you not upgrade to a slightly less out dated version of SQL Server (at least 2008 where you can use [table-valued parameters](http://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx))? — GarethD, Aug 19 '14 at 14:01
@Sean for 2005 and above [that splitter is not exactly the fastest](http://sqlperformance.com/2012/07/t-sql-queries/split-strings). I didn't perform any tests among only those methods that work for 2000. That splitter, for example, will not work on 2000 either... — Aaron Bertrand, Aug 19 '14 at 14:04
@AaronBertrand But in your testing you changed the input parameter to varchar(MAX). Jeff clearly states in his article that this will have a massive impact on performance. And of course CLR will pretty much blow anything away we can do with straight t-sql. ;) — Sean Lange, Aug 19 '14 at 14:11
@Sean ...because people will have parameters that are longer than 4000 or 8000 characters. It's 2014. That change wasn't made to rig the tests, it was made to support longer strings. I can run another series of tests without making that change if you like - do you really think it's going to change the performance? I don't. Nor does Jeff, apparently, who promised to come back with his findings and never did... — Aaron Bertrand, Aug 19 '14 at 14:19
@Sean the major problem with trying to compare performance on strings that are forced to be limited to 4K or 8K is that the difference is so negligible it's not worth testing. The scalability problems come in with much longer strings. The reason I point out this problem with Moden's splitter is because if people take his code, then change the parameters to accommodate their larger strings, they're going to discover the same thing I did: it doesn't scale. So it's a hard function to advocate in any case. — Aaron Bertrand, Aug 19 '14 at 14:26
Well, this is not a school where you can just complain to the Prof and ask him/her to use other later DB version. I'm at work and I need to do what my boss told me — C.J., Aug 19 '14 at 14:27
@Sean Perhaps you missed [this follow-up](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up) where that splitter still fared pretty poorly. — Aaron Bertrand, Aug 19 '14 at 14:28
In addition to that, my input is `'M06', 'R02', 'B19', 'C10'` as opposed to the other link where the input is `string, string, string` — C.J., Aug 19 '14 at 14:29
@AaronBertrand Sure CLR is going to be faster. I was trying to show that the typical xml or cte splitter is not a great solution because they are not as fast. I had missed your follow up article, thanks for the link. — Sean Lange, Aug 19 '14 at 14:40
@Sean Right and I was trying to press the point that when you can you shouldn't be doing this in T-SQL ***OR*** CLR but rather with TVPs ([final post in the series here](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql)). I still think it's bad to recommend a function that only works for small strings and can't possibly hope to scale, without a whole bunch of caveats. — Aaron Bertrand, Aug 19 '14 at 14:42
@Sean and again, CLR wasn't the only thing that was faster... in the case relevant for this specific problem in this question, it was more than 3X slower than *every other method tested*. So calling it "fast" in this context is just a little misleading IMHO. — Aaron Bertrand, Aug 19 '14 at 14:44

score 5 · Answer 1 · answered Jul 31 '20 at 19:51

Here's your savior! (5 years later)

Use a SQL Function!

What the following code do?

The function fn_split_string_to_column takes the list using a common separator and returns a table

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

SELECT * FROM empTable WHERE empCode IN (@employeeCode) -- this wont work!

SELECT * FROM empTable WHERE empCode IN (
    select value from fn_split_string_to_column(@employeeCode,',') -- this will work!
)

Examples!

Using comma as separator

declare @txt varchar(100) = 'a,b,c, d, e'
select * from fn_split_string_to_column(@txt,',')

Query results:

Using space as separator

declare @txt varchar(100) = 'Did you hear about the guy who lost his left arm and left leg in an accident? He’s all right now.'
select * from fn_split_string_to_column(@txt,' ')

Query results:

Source

The function above is not mine. I borrowed from the answer of the following question:How to split a comma-separated value to columns Go there and give him an upvote! (Actually you should go there and read the comments about performance issues you should know about)

score 0 · Answer 2 · edited Aug 19 '14 at 15:33

0

Or just use dynamic SQL. It would look something like this:

EXEC('SELECT * FROM empTable WHERE empCode IN (' + @employeeCode + ')')

edited Aug 19 '14 at 15:33

Andriy M

76,112
17
94
154

answered Aug 19 '14 at 13:54

John Herman

47
3

1

The moment I added the `'` around the parameter, it will treat the entire `+ @employeeCode +` as plain `string` and will no longer accept any input – C.J. Aug 19 '14 at 14:04

Stored Procedure parameter at WHERE IN clause

2 Answers2

Use a SQL Function!

What the following code do?

Examples!

Source