Passing Multivalue Parameter

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Adriaan Davel » Passing Multivalue Parameter

Passing Multivalue Parameter

Rate Topic

Display Mode

Topic Options

Author

Message

peer_mohamed2k

Posted Tuesday, November 06, 2007 9:54 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, August 07, 2012 4:43 AM
Points: 12, Visits: 57

How to pass Multivalue parameter in Reporting Services.

In my Report DataSet, I am using statement like this.
Exec SalesReport @SalesTerritory, @ProductName, @StartDate,
@EndDate

Here @SalesTerritoy and @ProductName are Multivalue parameters. And I am Selecting more than one value for the paramenters from the drop down.

In sql Procedure I am splitting the comma separated values and putting to temp table.

Thnx in Advance.

Post #419425

Elmer Miller

Posted Thursday, November 08, 2007 8:36 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 03, 2013 1:52 PM
Points: 977, Visits: 232

I'm not sure I understand your question, but it sounds like you need help with the sql procedure to make use of the multi-value parameter.
I usually do this by making the multi-value parameter a varchar(max) then in the query use the IN operator to filter the results like this:

select ...
from Sales
where ProductKey in (select convert(value,int) from dbo.Split(@ProductKeyList,',',default,default))

The Split function I use is:
--This UDF will split a delimited list into a table.
ALTER FUNCTION [dbo].[Split]
(
@list NVARCHAR(4000)
, @delimiter NVARCHAR(10) = N','
, @include_null BIT = 0
, @null_text NVARCHAR(10) = NULL
)
RETURNS @tableList TABLE(
idx SMALLINT IDENTITY (1,1) PRIMARY KEY,
value NVARCHAR(100) NULL
)
AS
BEGIN
DECLARE @value NVARCHAR(100)
DECLARE @position INT

SET @list = LTRIM(RTRIM(@list))+ @delimiter
SET @position = CHARINDEX(@delimiter, @list, 1)

IF REPLACE(@list, @delimiter, '') <> ''
BEGIN
WHILE @position > 0
BEGIN
SET @value = LTRIM(RTRIM(LEFT(@list, @position - 1)))
IF @include_null = 1 OR @value <> ''
BEGIN
IF @value = '' SET @value = @null_text
INSERT INTO @tableList (value)
VALUES (@value)
END
SET @list = RIGHT(@list, LEN(@list) + 1 - LEN(@delimiter) - @position)
SET @position = CHARINDEX(@delimiter, @list, 1)
END
END
RETURN
END

Post #420110

swiering

Posted Friday, November 16, 2007 8:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, November 16, 2007 9:01 AM
Points: 1, Visits: 2

Worked perfect for me, Elmer, Thank You!! :)

Post #423082

« Prev Topic | Next Topic »

Permissions