Posts
49
Comments
150
Trackbacks
0
Using Table-Valued Parameters With SQL Server Reporting Services

This blog post has moved:

http://gruffcode.com/2012/06/21/using-table-valued-parameters-with-sql-server-reporting-services/

posted on Thursday, June 21, 2012 10:08 PM Print
Comments
Gravatar
# re: Using Table-Valued Parameters With SQL Server Reporting Services
Michael Berhns-Miller
11/7/2012 9:26 AM
I was investigating precisely this problem and you answered it completely, thanks! Also agree that it's a bit cumbersome. I am going to try setting up a function, a view based on the function, and then use a where clause on the view from reporting services, I think it will be cleaner. Here we go... :-)
Gravatar
# re: Using Table-Valued Parameters With SQL Server Reporting Services
Michael Berhns-Miller
11/7/2012 1:55 PM
Turns out the function + view + where clause worked great - no looping through parameter array values in Reporting Services, and the view is very convenient for additional adhoc querying. What do you think Jesse?
Gravatar
# re: Using Table-Valued Parameters With SQL Server Reporting Services
Michael Behrns-Miller
11/7/2012 2:49 PM
By the way, I misspelled my own name earlier. DOH.
Gravatar
# re: Using Table-Valued Parameters With SQL Server Reporting Services
Aditya
12/6/2012 5:51 AM
The post is very helpful. I had a requirement to use table-value parameter within SSRS. Searched in google and became mad. Atlast found your solution. I have followed your method and able to see the output in Development studio. When i uploaded this to Report server then its throwing error: "Query executin failed for dataset. Column, parameter, or variable #1: Cannot find datatype integer_TableType". Please suggest solution for this.
Gravatar
# re: Using Table-Valued Parameters With SQL Server Reporting Services
Jesse
12/6/2012 8:33 AM
@Aditya: I'm glad the post was helpful.
The error you're getting is because you haven't yet setup the user defined type representing a table variable with a single integer column.

If you check out the post just before this one you'll see instructions on how to define the Integer_TableType in your database schema.
Here's a link to that other post:
http://wblo.gs/d0K
Gravatar
# re: Using Table-Valued Parameters With SQL Server Reporting Services
Aditya
12/6/2012 12:59 PM
Thank you Jesse for your quick response. I already created table type in the DB before working on the report. And good news is that its working now. There were similar Datasources in Report server, i pointed to wrong one. When i changed, it started working.
Gravatar
# re: Using Table-Valued Parameters With SQL Server Reporting Services
Aditya
3/5/2013 3:11 AM
Jesse... I implemented this method in a project a month back after going through your blog. Its working fine but sometimes its failing. Below is the error:
"An error has occurred during report processing.
Query execution failed for dataset 'DSOutput'.
String or binary data would be truncated. The statement has been terminated. "

I don't understand why its failing. Please suggest on this.
Gravatar
# re: Using Table-Valued Parameters With SQL Server Reporting Services
Jesse
3/5/2013 3:51 PM
@Aditya: I'd guess that your issue has to do with the number of items that you're selecting and passing into the report. I'd suggest posting your question on stackoverflow.com along with all of the relevant details of the report and the parameters. If you let me where you've posted the question I'd be happy to take a look and try to answer it for you there, or someone else might offer an answer.
Gravatar
# re: Using Table-Valued Parameters With SQL Server Reporting Services
Tavis Reddick
5/7/2013 10:23 AM
This was really helpful, thanks. Because our parameter list used string identifiers (which needed quotes) instead of integers, I duplicated your function with the slightly amended line:
insertStatements.AppendLine(String.Format("INSERT {0} VALUES ('{1}') ", variableName, paramValue))
Also, I had a problem with Reporting Services not having EXECUTE permission on the table type. This was sorted after looking at:
<http://connect.microsoft.com/SQLServer/feedback/details/355949/execute-permission-missing-on-user-defined-table-type>
Gravatar
# re: Using Table-Valued Parameters With SQL Server Reporting Services
Prashant
8/10/2013 8:13 AM
Thank you..Jesse.I have sucessfully done it for one TVP parameter, what could i do if there are more than one TVP's.
Gravatar
# re: Using Table-Valued Parameters With SQL Server Reporting Services
Gayatri
4/9/2015 9:10 AM
I have a similiar requirement where I need to pass a Table-valued paramter to the Stored Proc through SSRS. I followed all your steps as mentioned and I get an error "Parameter X is missing a value. Whaam I doing wrong? When I make the Parameter( which is set to Internal) visible, it works fine. Because it prompts me to select the value from the drop-down box

Post Comment

Title *
Name *
Email
Comment *  
Verification
Meta
Tag Cloud