Table valued parameters for SSRS 2008

Question

We have a requirement of generating SSRS reports from where we need to convert multi-valued string and integer parameters to datatable and pass it to stored procedure. The stored procedure contains multiple table type parameters. Earlier we used varchar(8000) but it was also crossing the datatype limit. Then we thought to introducing datatable concept. But we were not aware of how to pass values from SSRS.

We found a solution from GruffCode on Using Table-Valued Parameters With SQL Server Reporting Services.

The solution solved my problem, and we're able to generate reports. However, sometimes SSRS returns the two following errors:

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.

And

An unexpected error occurred in Report Processing.
Exception of type 'System.OutOfMemoryException' was thrown.

I'm not sure when and where it's causing the issue.

Answer 1

The approach outlined in that blog post relies on building an enormous string in memory in order to load all of the selected parameter values into the table-valued parameter instance. If you are selecting a very large number of values to pass into the query I could see it potentially causing the 'System.OutOfMemoryException' while trying to build the string containing the insert statements that will load the parameter.

As for the 'string or binary data would be truncated' error that sounds like it's originating within the query or stored procedure that the report is using to gather its data. Without seeing what that t-sql looks like I couldn't say why that's happening, but I'd guess that it's also somehow related to selecting a very large number of parameter values.

Unfortunately I'm not sure that there's a workaround for this, other than trying to see if you could figure out a way to select fewer parameter values. Here's a couple of rough ideas:

1. If you have a situation where users might select a handful of parameter values or all parameter values then you could have the query simply take a very simple boolean value indicating that all values were selected rather than making the report send all of the values in through a parameter.
2. You could also consider "zooming out" of your parameter values a bit and grouping them together somehow if they lend themselves to that. That way users would be selecting from a smaller number of parameter values that represent a group of the individual values all rolled up.

Answer 2

I'm not a fan of using a Text parameter and EXEC in the SQL statement like the article you referenced describes as doing so is subject to SQL injection. The default SSRS behavior with a Multi-value parameter substitutes a comma-separated list of the values directly in place of the parameter when the query is sent to the SQL server. That works great for simple IN queries, but can be undesirable elsewhere. This behavior can be bypassed by setting the Parameter Value on the DataSet to an expression of =Join(Parameters!CustomerIDs.Value, ", "). Once you have done that you can get a table variable loaded by using the following SQL:

DECLARE @CustomerIDsTable TABLE (CustomerID int NOT NULL PRIMARY KEY)

INSERT INTO @CustomerIDsTable (CustomerID)
SELECT DISTINCT TextNodes.Node.value(N'.', N'int') AS CustomerID
FROM (
    SELECT CONVERT(XML, N'<A>' + COALESCE(N'<e>' + REPLACE(@CustomerIDs, N',', N'</e><e>') + N'</e>', '') + N'</A>') AS pNode
    ) AS xmlDocs
CROSS APPLY pNode.nodes(N'/A/e') AS TextNodes(Node)

-- Do whatever with the resulting table variable, i.e.,
EXEC rpt_CustomerTransactionSummary @StartDate, @EndDate, @CustomerIDsTable

If using text instead of integers then a couple of lines get changed like so:

DECLARE @CustomerIDsTable TABLE (CustomerID nvarchar(MAX) NOT NULL PRIMARY KEY)

INSERT INTO @CustomerIDsTable (CustomerID)
SELECT DISTINCT TextNodes.Node.value(N'.', N'nvarchar(MAX)') AS CustomerID
FROM (
    SELECT CONVERT(XML, N'<A>' + COALESCE(N'<e>' + REPLACE(@CustomerIDs, N',', N'</e><e>') + N'</e>', '') + N'</A>') AS pNode
    ) AS xmlDocs
CROSS APPLY pNode.nodes(N'/A/e') AS TextNodes(Node)

-- Do whatever with the resulting table variable, i.e.,
EXEC rpt_CustomerTransactionSummary @StartDate, @EndDate, @CustomerIDsTable

This approach also works well for handling user-entered strings of comma-separated items.