Passing More than one value for a single parameter in Stored Procedure

The query for passing more than one value to a single parameter in a stored procedure is a most wanted for reporting purpose. Suppose we have a stored Procedure which have multiple value for a product in our sales value then we can use this function name and can pass multiple product in a single parameter product. This will be very useful when we use reporting service multivalue option.

Multivalue For Single Parameter Function:

For Reporting Purpose we want to select multiple values to a single parameter. In normal cases these are not allowed. To pass multiple values to a single parameter we can use this function GetListAsTable. Here there are two parameters Intergerlist to pass values. Example list of products like Two wheeler, Four Wheeler, Three Wheeler and next parameter is a comma delimeter
to separate values. Example the parameter will pass like 'Two wheeler,Four Wheeler, Three Wheeler'. And the function is given below.

CREATE FUNCTION [dbo].[UDF_GetListAsTable]
@IntegerList VARCHAR(Max),
@usecomma CHAR(1)
RETURNS @IntegersTable TABLE (IntegerFromList VARCHAR(50))

IF @IntegerList IS NULL
SELECT @IntegerList = ''

IF LEN(@IntegerList) > 0

DECLARE @ispacePosition INT
SELECT @ispacePosition = PATINDEX('%' + @usecomma + '%', @IntegerList)

WHILE @ispacePosition <> 0
--If nothing between delims, save as NULL
IF LEN(SUBSTRING(@IntegerList, 1, @ispacePosition - 1)) = 0
INSERT INTO @IntegersTable(IntegerFromList) VALUES (NULL)
INSERT INTO @IntegersTable(IntegerFromList)
VALUES (SUBSTRING(@IntegerList, 1, @ispacePosition - 1))

SELECT @IntegerList = SUBSTRING(@IntegerList, @ispacePosition + 1, LEN(@IntegerList))
SELECT @ispacePosition = PATINDEX('%' + @usecomma + '%', @IntegerList)
END --While...

--If nothing after final delims, save as NULL
IF LEN(@IntegerList) = 0
INSERT INTO @IntegersTable(IntegerFromList) VALUES (NULL)
INSERT INTO @IntegersTable(IntegerFromList)

END --IF...


We can use this above function in our stored procedures using this below query

DECLARE @Product Varchar(Max)
Set @Product = 'TW,FW'
DECLARE @ProductName TABLE (IntegerfromList VARCHAR(10) NOT NULL)
INSERT INTO @ProductName SELECT IntegerfromList from udf_GetListAsTable(@Product,',')
Select * from @ProductName


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: