x

SSRS 2005 Dataset stored procedure and multi-valued parameter

Hello, Is there a way in SSRS 2005 to use a stored procedure in a Dataset that accepts a multi-valued parameter?

For example, I’d like to pass one or more business units to my SP, let’s say 01 & 02. Through SQL Profiler I found that what is being passed to my SP is N'01,02'

My SP WHERE clause looks like the following: WERE BU IN (@parm_BUs) So at execution time I get WHERE BU IN ('01,02'). What I really want is WHERE BU IN ('01’,’02').

Thanks, Don

more ▼

asked Apr 21 '10 at 05:19 PM in Default

don 2 gravatar image

don 2
105 9 9 9

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You want to do some reading up on splitting parameters. Jeff Moden is your best bet over at SSC, with articles such as

The basic premise is to turn your multi-valued parameter into a table of values so you can then reference this is your query as a JOIN or WHERE IN clause

more ▼

answered Apr 21 '10 at 05:25 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

Sounds like SSRS 2005 doesn't support this functionality. In my case using a SP is a preference not a requirement therefore I think I'll place my code directly in the dataset rather than complicating things by using this cumbersome workaround.

Thank you for your help, Don
Apr 23 '10 at 04:32 PM don 2
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x535
x402
x72

asked: Apr 21 '10 at 05:19 PM

Seen: 2354 times

Last Updated: Apr 21 '10 at 05:33 PM