question

don 2 avatar image
don 2 asked

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

ssrsstored-proceduresparameters
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.