how to pass multiple values to single parameter

Hi,
You can pass comma separated value like 60,40,100 to your single parameter then in your sp you can call one UDF to split this value
Here is the UDF for splitting comma separated value

CREATE FUNCTION [dbo].[udf_SplitCsvToTable](@sText varchar(8000), @sDelim varchar(20) = ' ')

RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))

BEGIN

DECLARE @idx smallint,

@value varchar(8000),

@bcontinue bit,

@iStrike smallint,

@iDelimlength tinyint

IF @sDelim = 'Space'

BEGIN

SET @sDelim = ' '

END

SET @idx = 0

SET @sText = LTrim(RTrim(@sText))

SET @iDelimlength = DATALENGTH(@sDelim)

SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))

BEGIN

WHILE @bcontinue = 1

BEGIN

--If you can find the delimiter in the text, retrieve the first element and

--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0

BEGIN

SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)

BEGIN

INSERT @retArray (idx, value)

VALUES (@idx, @value)

END

--Trim the element and its delimiter from the front of the string.

--Increment the index and loop.

SET @iStrike = DATALENGTH(@value) + @iDelimlength

SET @idx = @idx + 1

SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

END

ELSE

BEGIN

--If you can't find the delimiter in the text, @sText is the last value in

--@retArray.

SET @value = @sText

BEGIN

INSERT @retArray (idx, value)

VALUES (@idx, @value)

END

--Exit the WHILE loop.

SET @bcontinue = 0

END

ELSE

BEGIN

WHILE @bcontinue=1

BEGIN

--If the delimiter is an empty string, check for remaining text

--instead of a delimiter. Insert the first character into the

--retArray table. Trim the character from the front of the string.

--Increment the index and loop.

IF DATALENGTH(@sText)>1

BEGIN

SET @value = SUBSTRING(@sText,1,1)

BEGIN

INSERT @retArray (idx, value)

VALUES (@idx, @value)

END

SET @idx = @idx+1

SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

END

ELSE

BEGIN

--One character remains.

--Insert the character, and exit the WHILE loop.

INSERT @retArray (idx, value)

VALUES (@idx, @sText)

SET @bcontinue = 0

END

RETURN

END

Regards,
Iftikar

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
Hi,
You can pass comma separated value like 60,40,100 to your single parameter then in your sp you can call one UDF to split this value
Here is the UDF for splitting comma separated value

CREATE FUNCTION [dbo].[udf_SplitCsvToTable](@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
END
ELSE
BEGIN
--If you can't find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END

END

RETURN
END
GO

Regards,
Iftikar