Passing an "IN" clause to a stored procedure

atcs2152 · February 1st, 2006, 09:31 AM

I'd like to create a stored procedure where one of the input parameters is the string for an IN clause. In this case the IN clause contains strings, not integers. For example:

Code:

CREATE PROCEDURE getEmployees @InClause varchar(100) AS
SELECT LastName, Salary
FROM Employees
WHERE LastName IN (@InClause)

I would then call the procedure like this:

Code:

execute getEmployees @InClause = "'Anderson','Smith','Jones'"

I can create the stored procedure fine but when I try to execute it, it doesn't return anything even though it should. Am I correct in assuming there's a way to do this without using dynamic SQL?

jbenson001 · February 3rd, 2006, 11:54 PM

I am really not sure why your code doesn't work. I would really like to know why. This code does work, althogh I am not sure if it is the best way. Let me know if you get your original code to work, or if you find out an answer as to why it does not work.

WHERE CHARINDEX( LastName , @InClause ) > 0

You can also remove the single quotes when passing in the string.

Jim

Imar · February 4th, 2006, 04:59 AM

There are ways to overcome this using a UDF that accepts the comma separated string and returns a table. You can then use this table to JOIN on with your other tables.

I thought there was an example at the Microsoft site, but I can't find it anymore. I found one very similar here: http://www.bigbold.com/snippets/tag/list#post1179

Usage can be as simple as:

SELECT * FROM Foo
WHERE FooBar IN (SELECT ListItem FROM dbo.fnSplit('Foo','Bar','FooBar'))

Using CharIndex is risky, as it might return unwanted results. For example, if you have a name of John that's matched against Johnson, you still get a result back.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

joefawcett · February 4th, 2006, 05:35 AM

The IN clause expects a comma delimited list.
You cannot pass a string containing commas.

You can use PATINDEX (or CHARINDEX) but tighten up the boundaries, although performance maybe poor.

Code:

DECLARE @InClause NVARCHAR(100)
SET @InClause = 'tom,dick,harry'
DECLARE @SafeInClause NVARCHAR(100)
SET @SafeInClause = ',' + @InClause + ',' 
SELECT * FROM myTable WHERE PATINDEX(',' + myColumn + ',', @SafeInClause) > 0

--

Joe (Microsoft MVP - XML)

atcs2152 · February 7th, 2006, 02:24 PM

I'm not familiar with UDFs or PATINDEXes. I guess I'll have to look into them. It seems like this is more complicated than it needs to be.

SQLScott · February 7th, 2006, 10:55 PM

Here's a table-valued udf example that will do what you need.

DROP FUNCTION udf_SplitIDString;
GO

create function udf_SplitIDString
(
  @IDString varchar(1000)
)

returns @IDs table
(ContactID int)
as

begin
declare @Position int
while len(@IDString) > 0
   begin
     set @Position = charindex(',', @IDString)
     if @Position > 0
       begin
         insert @IDs
         select convert(int, left(@IDString, @Position - 1))
         set @IDString = right(@IDString, len(@IDString) - Position)
       end
     else
       begin
         insert @IDs
         select convert(int, @IDString)
         set @IDString = ''
       end
   end
return
end

DECLARE @userids varchar(2000)
SET @userids = '1, 2, 3, 4, 5'
SELECT a.*
FROM Person.Contact a
inner join udf_SplitIDString(@userIDs) b
on a.ContactID = b.ContactID