Hi, How can we use Stored Procedure with Table Valued Parameter in SSRS Dataset ? CREATE TYPE DBO.MultiFilter AS TABLE ( filterVal VARCHAR(200) ) CREATE PROCEDURE DBO.USP_multiFilterTest @position DBO.MultiFilter READONLY AS BEGIN SELECT * FROM @position END If I use this SP DBO.USP_multiFilterTest in Data Set , it asking parameter value. Which values I can pass to this parameter ?

SSRS supports only scalar values for parameters, not table values. Olaf

SSRS - STORED PROCEUDURE WITH TABLE VALUED PARAMETERS

Accepted answer

Olaf Helper 43,161 Reputation points

2020-08-19T12:24:27.96+00:00

SSRS supports only scalar values for parameters, not table values.

Olaf
Please sign in to rate this answer.

1 person found this answer helpful.
Srinivasa Rao Chigulla 46 Reputation points

2020-08-19T12:36:00.897+00:00

Thank you for your answer.

Can you please explain how to handle multi selected parameter values in SSRS (other than SPLIT function)?

I heard about TVP(table valued parameters). But Can't find out how to use these parameters in SSRS.

scott_morris-ga 16 Reputation points

2020-08-19T17:00:03.59+00:00

As Olaf already wrote - you can't. SSRS does not support the use of a TVP. SSRS will pass a multi-valued parameter as a CSV string to a stored procedure. Example / discussion passing-multivalue-parameter-in-stored-procedure-ssrs.

Srinivasa Rao Chigulla 46 Reputation points

2020-08-20T09:30:03.653+00:00

Thanks for reply.

Is there any other way to handle multi valued parameters other than using SPLIT function?

If the filter data already having ,(coma) separated data , then how to we handle this ?

Example :
Name Filter : Jhon, Stevenson
Stephen, walker
.......................

In this case how to handle filters data ?

Viorel 114.5K Reputation points

2020-08-20T09:39:16.63+00:00

You can use a character that is never present, for example ‘|’. If you build the string programmatically, then you can use some non-printable character, such as CHAR(1), if your code is SQL.

Programmatically you can also build and pass an XML or JSON. Maybe this is possible in SSRS too.

scott_morris-ga 16 Reputation points

2020-08-20T12:12:33.043+00:00

Unfortunately now your question is one that is specific to SSRS, not tsql. So how can you override the default behavior of SSRS in its usage of multi-value parameters? I don't know and that doesn't seem like a question related to the this (tsql) forum. The easy answer is to simply "purify" the values you use for selecting by removing the commas. Perhaps a lookup table approach where you pass IDs or hashes rather than actual display strings (which is the usual approach)?

Allen M 11 Reputation points

2020-08-21T18:29:45.693+00:00

What we do: When you pass the parameter to your stored procedure convert it to a joined string. We use the tilda~ incase of commas in the value. In your stored procedure split that string apart.

=JOIN(Parameters!mymulitvalueParameter.Value,"~")

Also make sure you actually need a stored procedure and can't just put the query into the report.
Sign in to comment

Share via

SSRS - STORED PROCEUDURE WITH TABLE VALUED PARAMETERS

0 additional answers