Passing Multiple Values to a Single Parameter in SQL Server Stored Procedures: SSRS Integration and String Splitting Techniques

Problem Background and Challenges

In SSRS (SQL Server Reporting Services) reports, developers often execute stored procedures to generate data. However, when attempting to pass multiple values to a single parameter of a stored procedure, technical limitations arise. For example, in the issue, the user tried to pass two portfolio IDs via @PortfolioId = '5,6', but the result returned an incorrect record count instead of the expected 190 records (120 from ID 5 and 70 from ID 6). This stems from the nature of SQL Server stored procedure parameters: they are designed to accept single values, not arrays or lists.

SSRS Limitations on Multi-Value Parameters

According to Microsoft official documentation, SSRS supports multi-value parameters, but only for specific data sources (e.g., SQL Server, Oracle, Analysis Services), and cannot be used directly with stored procedures. This is because SSRS cannot pass multi-value parameter arrays to stored procedures. Instead, queries must use IN clauses to handle multiple values. In the issue, the stored procedure GENERATE_REPORT defines a VARCHAR(50) parameter @PortfolioId with a default value of '2', but the user expected it to handle comma-separated strings like '5,6'.

Core Solution: String Splitting Techniques

To address this, string splitting logic must be implemented within the stored procedure. The best answer (Answer 1) proposes a method based on loops and temporary tables. Pseudo-code illustrates how to split a comma-separated string into individual integer values:

CREATE TABLE #TempTable (ID INT)
WHILE LEN(@PortfolioID) > 0
BEGIN
    IF NOT <@PortfolioID contains Comma>
    BEGIN
        INSERT INTO #TempTable VALUES CAST(@PortfolioID as INT)
        SET @PortfolioID = ''
    END ELSE
    BEGIN
         INSERT INTO #Temptable VALUES CAST(<Part until next comma> AS INT)
         SET @PortfolioID = <Everything after the next comma>
    END
END

In practice, built-in functions like STRING_SPLIT (available in SQL Server 2016 and later) or custom user-defined functions (UDFs) can be used. For instance, the stored procedure in the issue uses a UDF ParseMultiValuedParameter to parse @PortfolioId, but it might not be applied correctly in the join condition, leading to incorrect record counts. The corrected query should ensure the IN clause properly references the parsed values:

ClosingPrice.PortfolioID IN (
    SELECT PARAM
    FROM Fireball_Reporting.dbo.ParseMultiValuedParameter(@PortfolioId, ',')
)

Code Examples and In-Depth Analysis

To clarify, we rewrite part of the stored procedure code to show a complete string splitting implementation. Assuming the use of SQL Server 2016+'s STRING_SPLIT function:

-- Add logic in the stored procedure
DECLARE @SplitValues TABLE (PortfolioID INT)
INSERT INTO @SplitValues
SELECT value
FROM STRING_SPLIT(@PortfolioId, ',')
WHERE value <> ''

-- Use in the query
SELECT 
    Position.Date,
    Position.SecurityId,
    Position.Level1Industry
FROM Fireball_Reporting.dbo.Reporting_DailyNAV_Pricing POSITION
LEFT JOIN Fireball.dbo.AdditionalSecurityPrice ClosingPrice ON
    ClosingPrice.SecurityID = Position.PricingSecurityID AND
    ClosingPrice.Date = Position.Date AND
    ClosingPrice.SecurityPriceSourceID = @SourceID AND
    ClosingPrice.PortfolioID IN (SELECT PortfolioID FROM @SplitValues)

This approach avoids type mismatch issues from passing strings directly and ensures the query returns only records for the specified IDs. In the issue, errors might stem from the ParseMultiValuedParameter function not handling nulls or delimiters correctly, or from flawed join logic introducing extra records.

Supplementary References and Alternative Methods

Other answers provide additional insights. Answer 2 suggests using UDFs, which is useful for older SQL Server versions or environments without STRING_SPLIT support. For example, a UDF can be created to split strings and return table-valued results. Answer 3 cites an external resource, emphasizing that integrating multi-value parameters in SSRS requires adjusting report parameter settings to pass comma-separated strings to stored procedures. However, all these methods revolve around the same core: parsing strings within the stored procedure, rather than relying on SSRS to pass arrays directly.

Performance and Best Practices

When handling multi-value parameters, performance is a key consideration. Using temporary tables or table variables to store split values can enhance query efficiency, especially with large datasets. Additionally, ensuring parameter validation (e.g., checking for nulls or invalid characters) prevents runtime errors. On the SSRS side, developers should set parameters as multi-value types and use expressions like Join(Parameters!PortfolioId.Value, ",") to convert arrays to comma-separated strings before passing them to stored procedures.

Conclusion and Summary

Passing multiple values to a single parameter in SQL Server stored procedures is a common yet challenging issue, particularly in SSRS environments. Through string splitting techniques, such as using the STRING_SPLIT function or custom UDFs, developers can effectively parse comma-separated strings and filter data with IN clauses in queries. This resolves the record count errors seen in the original problem, ensuring report accuracy. Meanwhile, attention to SSRS limitations and code escaping rules (e.g., correctly representing <br> tags as text in documentation) is crucial for maintaining clear technical documentation.