SQL 2005 - Pass a comma delimited parameter to a SP and return varchar to be used in a where IN clause.
Posted on 2011-05-10
This should be the easiest points you've gotten on here. I'm an Access guy and could do this in VBA with my eyes closed, but when it comes to SQL Server, I'm a complete newbee...
All I want to do is pass a comma delimited set of values (ie.. ITEM1,ITEM2,ITEM3,ITEM4) to a stored procedure and have it return a varchar that I can use in a where clause (ie.. '''ITEM1'',''ITEM2'',''ITEM3'',''ITEM4''')
I also need to know how to call that SP from within another SP (I told you I was a newbee)...
Example portion of code from calling SP:
@ITEM = CALL SP TO PARSE THE STRING AND RETURN VALUE IN THE FORMAT IT'S NEEDED IN.
Here's my attempt at it, but I clearly don't have a clue. Maybe I'm close, maybe not... All I know is I need this ASAP.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[SP_ParseString]
@string varchar(500) = NULL
,@delimeter char(1) = NULL
SET NOCOUNT ON;
if right(rtrim(@string),1) <> @delimeter
set @string = @string + @delimeter
set @pos = patindex('%' + @delimeter + '%' , @string)
while @pos <> 0
set @piece = '''' + left(@string, @pos - 1) + ''''
set @buildpiece = @piece
set @buildpiece = @buildpiece + ', ' + @piece
set @string = stuff(@string, 1, @pos, '')
set @pos = patindex('%,%' , @string)