Link to home
Start Free TrialLog in
Avatar of David Glover
David GloverFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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.
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

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

Avatar of David Glover

ASKER

Thanks Kyle, is this likely to outperform dynamic SQL?
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Scott, I adopted this in my solution!
Regards,