2

I have a stored procedure that I need to pass a comma-delimited variable to.

For example, if a certain condition exists, I would pass a list of country codes like this:

AU,RA,PK

The number of items can vary.

In the stored procedure, I need to use those items in an IN clause such as follows:

WHERE CountryCode IN (@ExcludeCountries)

Is there any way to do this? I can massage the country codes going in to something like N'AU', N'RA', N'PK' if need be.

Thanks.

Yosi Dahari
  • 6,794
  • 5
  • 24
  • 44
Tom
  • 4,467
  • 17
  • 59
  • 91
  • 2
    There are a million duplicates of this... short answer is to pass a table if you can or use one of the parsing function out there. – Hogan Nov 26 '13 at 18:35

3 Answers3

0
WHERE CountryCode IN (SELECT *
                      FROM [dbo].[ufn_CSVToTable](@ExcludeCountries,',')

This is how you can create [dbo].[ufn_CSVToTable]:
How to convert comma separated NVARCHAR to table records in SQL Server 2005?

Community
  • 1
  • 1
Yosi Dahari
  • 6,794
  • 5
  • 24
  • 44
0

In practice I've seen this problem resolved using the function below. This function splits the string and return a table containing the values specified. Then use it like this...

...CountryCode IN (SELECT * FROM dbo.SplitString(@ExcludeCountries ,','))

Notice that in this example the comma (',') is the delimiter. I've used this method for years and I haven't encounter any performance problems.

CREATE FUNCTION [dbo].[SplitString]
(
@SplitStr nvarchar(MAX),
@SplitChar nvarchar(5)
) 
RETURNS @RtnValue table 
(
Data nvarchar(MAX)
) 
AS 
BEGIN 
Declare @Count int
Set @Count = 1

While (Charindex(@SplitChar,@SplitStr)>0)
Begin
Insert Into @RtnValue (Data)

Select 
Data = ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1)))

Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr))
Set @Count = @Count + 1
End

Insert Into @RtnValue (Data)
Select Data = ltrim(rtrim(@SplitStr))

Return
END
fossilcoder
  • 59
  • 6
  • This solution is already suggested in my answer, why repeating it? – Yosi Dahari Nov 26 '13 at 19:46
  • I didn't notice it until I wrote my answer. Still my answer is complete, no need to figure out other question. – fossilcoder Nov 26 '13 at 20:06
  • BTW, as you can see, I'm new here, and I don't think that a duplicate solution is enough to get a down vote, it still solves the problem presented, and its a complete solution. – fossilcoder Dec 17 '13 at 15:12
-1

There are two ways to do that:

  1. Using the @ExcludeCountries parameter build table variable table that is latter join to your query

  2. Build a dynamic SQL statement like this 

    ... + 'WHERE CountryCode IN' (' + @ExcludeCountries + ')' ...

    and then use sp_executesql to exec the statement

gotqn
  • 42,737
  • 46
  • 157
  • 243
  • But avoid it - There is no need to use dynamic SQL for such a simple task. – Yosi Dahari Nov 26 '13 at 18:41
  • @Yosi Completely disagree. I have done this a lot of times and it always depends from the situation. The solution that you have provided can lead to bad performance in some cases, while using a dynamic SQL you can fix that - it depends. – gotqn Nov 26 '13 at 18:43
  • In most cases when using Dynamic Sql - You lose out on parameter protection, code readability, query compilation and execution plan caching, and probably bounce of other things. You can avoid all of those (or even perform better), but it's more complex. My solutions can lead to bad performance when there are thousands of values in the subquery, did the user described such situation? I don't think so... – Yosi Dahari Nov 26 '13 at 18:51
  • Dynamic SQL is always a poor choice when other options exist. The best solution is of course none of these -- pass a table parameter. – Hogan Nov 26 '13 at 19:33