Sql Server Pass Comma Seperated Parameter in Procedure

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s