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 45,106 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 118K 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

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SSRS - STORED PROCEUDURE WITH TABLE VALUED PARAMETERS

0 additional answers

Your answer