CREATE PROCEDURE sp_MultiValueParam
@ParamStr	Varchar(500)		-- comma delimited string
AS

/*--------------------------------------------------------------------------------------------------------------------

Created By	: Sumit Dhingra
Date Created	: 07/23/2001
Last Modified	: 07/23/2001
Purpose		: To extract Individual values from a comma Delimited String passed as a parameter. 
		  Allows user to make a single call to the SP and pass in multiple values.
Benefit		: Avoids multiple calls to the SP.

USAGE:
DECLARE @ParamStr	Varchar(500)

SET @ParamStr = 'Mango, Grapes, Banana, Peach'
exec sp_MultiValueParam @ParamStr

--------------------------------------------------------------------------------------------------------------------*/

SET NOCOUNT ON

/* Declare Variables	*/
DECLARE @Fruit			Varchar(25)		
DECLARE @ctr			Int
DECLARE @posctr			Int
DECLARE @commaPos		Int
DECLARE @paramLen		Int
DECLARE @findposinparamstr	Varchar(200)


/* Initialize the Variables	*/
SET @paramLen = len(@ParamStr)
SET @findposinparamstr = @ParamStr
SET @posctr = 1
SET @ctr = 0


/* Loop through the Comma Delimited String and pick up the individual values (Fruits)	*/
WHILE @posctr < @paramLen
BEGIN
	SET @commaPos = patindex('%,%',@findposinparamstr)

	IF (@commaPos <> 0)
	BEGIN
		SET @Fruit = ltrim(substring(@ParamStr,@posctr,@commaPos-1))
		Print @Fruit
	END
	ELSE			/* @commaPos <> 0	*/
		Break

	SET @posctr = @posctr + @commaPos 			-- Set this to starting position for next element.
	SET @findposinparamstr = substring(@findposinparamstr,(@commapos + 1),(len(@findposinparamstr)-@commapos))
	SET @ctr = @ctr + 1
END				/* End of While....	*/


/* Get the last item from the string	*/
SET @Fruit = ltrim(@findposinparamstr)
Print @Fruit

SET NOCOUNT OFF
Return(0)
GO