Passing an array into an IN clause in a Stored Proc

Hello Experts.  I have a Stored Procedure that I need to be able to pass a parameter into an IN clause.  

Create Procedure dbo.MyProc

	@ProdCode Varchar(8)
Select	*
From	dbo.Products
Where	ProdCode In (@ProdCode)


Exec dbo.MyProc

Open in new window

From what I understand I may have to create a function to pass this through.  Any direction here is greatly appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

-- First, create this function:

-- Returns a single-column table from a delimited string
CREATE FUNCTION [dbo].[csv1] (@Source VARCHAR(MAX), @rowDelim VARCHAR(max)=',')
    RETURNS @Result TABLE (ID int identity, col1 varchar(MAX))
      IF isNull(@rowDelim,'') in (char(10),'')
            SELECT @rowDelim = char(13)+char(10)
            SELECT @Source = replace( @Source, @rowDelim+char(13), @rowDelim )
            SELECT @Source = replace( @Source, @rowDelim+char(10), @rowDelim )

      SELECT @Source = rtrim(ltrim(@Source))
      SELECT @Source = @Source+@rowDelim  WHERE RIGHT(@Source,1) != @rowDelim

      DECLARE @rowDelimPos int=0, @StartShift int=1, @LenSource int=len(@Source)
      WHILE @StartShift <= @LenSource
            SELECT @rowDelimPos = charindex( @rowDelim, @Source, @StartShift )
            INSERT INTO @Result VALUES ( substring(@Source,@StartShift,@rowDelimPos-@StartShift) )
            --INSERT INTO @Result VALUES ( nullIf(substring(@Source,@StartShift,@rowDelimPos-@StartShift),'') )
            SELECT @StartShift = @rowDelimPos + len(@rowDelim)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-- Then, in your procedure you can do this:

Select      *
From      dbo.Products
Where      ProdCode In (select col1 from dbo.csv1(@ProdCode))
-- correction, need to pass in the delimiter to the function:

Where      ProdCode In (select col1 from dbo.csv1(@ProdCode, ',' ))
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

jobprojnAuthor Commented:
Thanks for the reply knightEknight.  I ran it and this is only passing the first parameter through the IN clause.  From my example, the query only returns values for LITE.  The SINK and FAUCET values don't get make it in.
please run this test and post the result:

select * from dbo.csv1( 'LITE,SINK,FAUCET', ',' )
jobprojnAuthor Commented:
It returns as rows in a table.

ID      col1
1      LITE
2      SINK
3      FAUCET
jobprojnAuthor Commented:
Got it.  The parameter @ProdCode was set to varchar(8), which was cutting off the string when when the parameter was used in the stored procedure.  Changed it to varchar(2000) and all is good.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.