Multivalue parameter to stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Multivalue parameter to stored proc

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-07-21 : 17:46:00

Hi All -
I am using SSRS 2008 and have the situation with a report where one of the four report parameters allows for multiple values. The end user could select ALL or just one as well.

With the first three parameters, I could simply use a COALESCE in my stored proc, like:

t1.CustType = COALESCE(@CustType, t1.CustType)

However, with the multivalue parameter, I'd have to use the "IN" clause and then I think that I'd have to use Dynamic SQL to build the WHERE clause.

Has anyone else had to do with SSRS, multivalue parameters and a stored proc?

Thanks for any help or suggestions.
- Will

iqmcl
Starting Member

8 Posts

Posted - 2010-07-22 : 09:38:23

I've had a similar situation and I passed the values into a split function and can then use the in clause on the results. Here is the split function;

CREATE FUNCTION [dbo].[Split]
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS

BEGIN

DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @Item NVARCHAR(4000)

SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN

IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END

and then you can use this in the SP;

SELECT * FROM TABLE WHERE FIELD
IN (Select Item from dbo.Split(@MultiValueParam,'|'))

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-07-22 : 11:02:04

quote:
Originally posted by iqmcl

I've had a similar situation and I passed the values into a split function and can then use the in clause on the results. Here is the split function;

Yeah, I should have updated my topic as that is what I ended up doing yesterday - creating a function to create a table of values and then a subquery in the Where clause. It is unfortunate because one my selections is "ALL" values. If it is "ALL", then I'd rather not even include that in my Where clause. This is one of the reasons (performance, being another) that I am considering returning to Dynamic SQL. Well, I'll see where things lead.

thanks.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-08-01 : 22:11:16

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110163

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-02 : 04:08:03

I'm not too familiar with reporting services but using .net you could pass a table valued parameter -> http://msdn.microsoft.com/en-us/library/bb510489(SQL.100).aspx. The concept is basically to create a table variable out of the multiple parameters and then pass that to the report.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 02:09:52

quote:
Originally posted by dhw

quote:
Originally posted by iqmcl

I've had a similar situation and I passed the values into a split function and can then use the in clause on the results. Here is the split function;

Yeah, I should have updated my topic as that is what I ended up doing yesterday - creating a function to create a table of values and then a subquery in the Where clause. It is unfortunate because one my selections is "ALL" values. If it is "ALL", then I'd rather not even include that in my Where clause. This is one of the reasons (performance, being another) that I am considering returning to Dynamic SQL. Well, I'll see where things lead.

thanks.

nope its just of putting an OR condition like

WHERE (field IN (select val from dbo.Splitfunction(@Yourparamvalue,',') OR @Yourparamvalue='ALL')
....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kokkula
Starting Member

41 Posts

Posted - 2010-08-05 : 06:23:30

Hello,

You can also use LIKE operator, sample below

SELECT *
FROM
(
SELECT 1 A
UNION SELECT 16 A
UNION SELECT 21 A
) A
WHERE ',21,16,' LIKE '%,'+ CAST(A AS NVARCHAR) + ',%'

Hope its helpful....

Pavan
Infosys Technologies Limited

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources