i had problem as u have like returning only first value of the multi value parameter. It is because when you write the parameter value it will select like parameter!value(0) for mutli value parameter and this means it will take only first string of array. Make sure it doesnt use (0) into parameter value.
Then Create this function.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- This one is for passing a group of string values
CREATE FUNCTION [dbo].[fn_MVParamChar]
(@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(100)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Param) VALUES(Cast(@Piece AS varchar(100)))
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END
--------------------------------------------------------------
now use this function as below into your stored procedure when you pass your multi value parameter.
yourfield IN (Select Param FROM [dbo].[fn_MVParamChar](@yourparameter, ','))
regards,
vijay