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))
AS
BEGIN
IF @IntegerList IS NULL
SELECT @IntegerList = ''
IF LEN(@IntegerList) > 0
BEGIN
DECLARE @ispacePosition INT
SELECT @ispacePosition = PATINDEX('%' + @usecomma + '%', @IntegerList)
WHILE @ispacePosition <> 0
BEGIN
--If nothing between delims, save as NULL
IF LEN(SUBSTRING(@IntegerList, 1, @ispacePosition - 1)) = 0
INSERT INTO @IntegersTable(IntegerFromList) VALUES (NULL)
ELSE
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)
ELSE
INSERT INTO @IntegersTable(IntegerFromList)
VALUES(@IntegerList)
END --IF...
RETURN
END
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