How to Pass Multi-Value Parameters to SQL Server Stored Procedure
I got This Article From http://www.dotnetspider.com/
To use Comma Seperated parameter value like 1,2,3 Make Following Function in Sql Server
ALTER FUNCTION [dbo].[fn_Split_Up_Ids]
(
@Param_Ids varchar(500)
)
RETURNS @Id_Table TABLE(IDField int)
AS
BEGIN
IF (LEN(@Param_Ids) <= 0)
RETURN
DECLARE @CommaPos smallint
SET @CommaPos = CHARINDEX(‘,’, RTRIM(LTRIM(@Param_Ids)))
IF @CommaPos = 0
INSERT INTO @Id_Table
VALUES(CONVERT(BIGINT ,RTRIM(LTRIM(@Param_Ids))))
ELSE
BEGIN
WHILE LEN(@Param_Ids) > 1
BEGIN
SET @CommaPos = CHARINDEX(‘,’, RTRIM(LTRIM(@Param_Ids)))
INSERT INTO @Id_Table
VALUES(CONVERT(INT ,SUBSTRING(RTRIM(LTRIM(@Param_Ids)),1, @CommaPos – 1)))
SET @Param_Ids = SUBSTRING(RTRIM(LTRIM(@Param_Ids)), @CommaPos + 1 , LEN(RTRIM(LTRIM(@Param_Ids))))
SET @CommaPos = CHARINDEX(‘,’, RTRIM(LTRIM(@Param_Ids)))
IF @CommaPos = 0
BEGIN
INSERT INTO @Id_Table VALUES(CONVERT(INT ,RTRIM(LTRIM(@Param_Ids))))
BREAK
END
END
END
RETURN
END
And From Above Function
To use the id in the IN clause of the SELECT statement
SELECT * FROM COUNTRY
WHERE Country_Id IN (SELECT * FROM dbo.fn_Split_Up_Ids(‘9,4,2,6,10’))
if you use this values from parameter than
SELECT * FROM COUNTRY
WHERE Country_Id IN (@Country_List)
But this will not work if you write this query in a stored procedure. Therefore you need to use the above function to pass the multi-value paramters to a stored procedure.
The Above Function returns data in integer so for parameter which are in varchar use below function
This Function will return value in Varchar
Alter FUNCTION [dbo].[fn_Split_Up_Ids]
(
@Param_Ids varchar(500)
)
RETURNS @Id_Table TABLE(IDField varchar(1000))
AS
BEGIN
IF (LEN(@Param_Ids) <= 0)
RETURN
DECLARE @CommaPos smallint
SET @CommaPos = CHARINDEX(‘,’, RTRIM(LTRIM(@Param_Ids)))
IF @CommaPos = 0
INSERT INTO @Id_Table
VALUES (RTRIM(LTRIM(@Param_Ids)))
ELSE
BEGIN
WHILE LEN(@Param_Ids) > 1
BEGIN
SET @CommaPos = CHARINDEX(‘,’, RTRIM(LTRIM(@Param_Ids)))
INSERT INTO @Id_Table
VALUES(SUBSTRING(RTRIM(LTRIM(@Param_Ids)),1, @CommaPos – 1))
SET @Param_Ids = SUBSTRING(RTRIM(LTRIM(@Param_Ids)), @CommaPos + 1 , LEN(RTRIM(LTRIM(@Param_Ids))))
SET @CommaPos = CHARINDEX(‘,’, RTRIM(LTRIM(@Param_Ids)))
IF @CommaPos = 0
BEGIN
–INSERT INTO @Id_Table VALUES(CONVERT(INT ,RTRIM(LTRIM(@Param_Ids))))
INSERT INTO @Id_Table VALUES(RTRIM(LTRIM(@Param_Ids)))
BREAK
END
END
END
RETURN
END
Nilesh Rathod 