Passing a string of comma seperated values to use in a stored procedure as part of an IN statement.

The question of getting a stored procedure to take a string like '12,13,14,16', we'll say @CommaDelimitedString and then turning that into something like the select statement below has been asked a lot from googling a bit.
select X from Y where X in(@CommaDelimitedString)
Clearly the above does not work so you have to choose from breaking up the string into a table by parsing or looping or to use dynamic SQL.
Dynamic SQL is the tidiest by far but obviously has some injection risk but from a performance perspective which approach would be best?
In my scenario the table being scanned is 4-5 million records long.  Would avoiding dynamic SQL allow the stored procedure to optimize more effectively and therefore justify a lengthier parsing stage or would this be virtually pointless?
Perhaps someone can comment.
I am using SQL server 2012.
dgloverukAsked:
Who is Participating?
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.

Kyle AbrahamsSenior .Net DeveloperCommented:
Third option:  Call a functiion.

select X from y where X in
(select * from dbo.fn_txt_split(@CommaDelimitedString))

/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 06/04/2015 12:58:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




Create Function [dbo].[fn_Txt_Split]( 
    @sInputList varchar(max) -- List of delimited items 
  , @Delimiter char(1) = ',' -- delimiter that separates items 
) 
RETURNS @list table (Item varchar(max)) 
as begin 
DECLARE @Item Varchar(max) 
  
  

WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0 
BEGIN 
SELECT 
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0 
)-1))), 
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList)))) 
  
IF LEN(@Item) > 0 
INSERT INTO @List SELECT @Item 
  
END 

  
IF LEN(@sInputList) > 0 
INSERT INTO @List SELECT @sInputList -- Put the last item in 
  
return 
END 



GO

Open in new window

0
dgloverukAuthor Commented:
Thanks Kyle, is this likely to outperform dynamic SQL?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
I use it all over the place.  The nice thing about it is that it's clean and easy to read.  The table is created and then is filtered by the where in the main query . . . so the only performance hit is the function call versus doing it all in one.  A small price to pay for a clarity and maintainability.
0

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
Scott PletcherSenior DBACommented:
No, it won't perform all that well, since it's a multi-line table-valued function.

Get DelimitedSplit8K, a well-known, in-line tvf splitter that's extremely fast.

You can join to the split results, like so:

SELECT ...
FROM main_table mt
INNER JOIN dbo.DelimitedSplit8k ( @parameter_list, ',' ) AS ds ON
    ds.Item = mt.<whatever_column>
WHERE ...

>> In my scenario the table being scanned is 4-5 million records long. <<
Yikes; let's hope you don't have to scan 4+M rows every time.
0
dgloverukAuthor Commented:
Thanks Scott, I adopted this in my solution!
Regards,
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.