Multi-Value Report Parameters in T-SQL Without Dynamic SQL

Multi-value report parameters do not need dynamic SQL. You can pass a list like 1, 2, 3 as plain text, split it, check every piece, and filter with EXISTS. The list stays data from start to finish.

The report that sends a list

A report tool lets users tick several regions. It sends one string, for example 1, 2, 2, 3. The lazy fix is to paste that string into an IN list inside dynamic SQL. It works until the day someone sends something that is not a number.

I want a static query instead. First, some demo data: four regions and a few sales. The demo creates two small tables and drops them at the end.

DROP TABLE IF EXISTS dbo.Sales;
DROP TABLE IF EXISTS dbo.Regions;
CREATE TABLE dbo.Regions (RegionId int PRIMARY KEY, RegionName nvarchar(30));
CREATE TABLE dbo.Sales (SaleId int PRIMARY KEY, RegionId int, Amount decimal(12,2));

INSERT dbo.Regions VALUES (1, N'North'), (2, N'South'), (3, N'West'), (4, N'East');
INSERT dbo.Sales VALUES (1, 1, 100), (2, 1, 50), (3, 2, 200),
                        (4, 3, 300), (5, 4, 400), (6, 2, 25);

Why a plain join multiplies rows

The obvious way to use STRING_SPLIT is to join to it. Watch what happens when the user ticks South twice, which a report tool will happily do.

DECLARE @RegionIds nvarchar(max) = N'1, 2, 2, 3';

SELECT r.RegionName, SUM(s.Amount) AS Total
FROM dbo.Regions AS r
JOIN dbo.Sales AS s ON s.RegionId = r.RegionId
JOIN STRING_SPLIT(@RegionIds, N',') AS l ON TRY_CONVERT(int, l.value) = r.RegionId
GROUP BY r.RegionName
ORDER BY r.RegionName;

SELECT r.RegionName, SUM(s.Amount) AS Total
FROM dbo.Regions AS r
JOIN dbo.Sales AS s ON s.RegionId = r.RegionId
WHERE EXISTS (SELECT 1 FROM STRING_SPLIT(@RegionIds, N',') AS l
              WHERE TRY_CONVERT(int, l.value) = r.RegionId)
GROUP BY r.RegionName
ORDER BY r.RegionName;

South has sales of 225. The join version reports 450, because South matched two tokens and every sale was counted twice. The EXISTS version reports 225. EXISTS only asks “is this region in the list?” and the answer is yes or no, however many times it appears.

That is the bug nobody notices. The report still looks fine. The totals are just wrong.

Check every token before you trust it

Now the second trap. Split a list that has an empty spot and a typo, and convert each token. I use the ordinal option so the rows come back in the order you typed them.

SELECT ordinal, value AS Token, TRY_CONVERT(int, value) AS AsInt
FROM STRING_SPLIT(N'1,,x,3', N',', 1)
ORDER BY ordinal;

The token x becomes NULL, which is easy to catch. The empty token becomes 0, which is not. TRY_CONVERT turns an empty string into zero, so a list like 1,,3 would quietly ask for region 0. My fix is to turn empty text into NULL with NULLIF before converting. Then every bad token ends up as NULL, and one check catches them all.

Put it in a procedure

Here is the whole contract in one place. Empty input is an error, not a hidden “all regions.” If you want an All option, give it its own parameter and its own rules.

CREATE OR ALTER PROCEDURE dbo.RegionReport @RegionIds nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    IF @RegionIds IS NULL OR TRIM(@RegionIds) = N''
        THROW 50000, N'Select at least one region.', 1;

    SELECT TRIM(value) AS Token,
           TRY_CONVERT(int, NULLIF(TRIM(value), N'')) AS RegionId
    INTO #Selected
    FROM STRING_SPLIT(@RegionIds, N',');

    IF EXISTS (SELECT 1 FROM #Selected WHERE RegionId IS NULL)
        THROW 50000, N'Use a comma-separated list of integer IDs.', 1;

    SELECT r.RegionId, r.RegionName, SUM(s.Amount) AS Total
    FROM dbo.Regions AS r
    JOIN dbo.Sales AS s ON s.RegionId = r.RegionId
    WHERE EXISTS (SELECT 1 FROM #Selected AS x WHERE x.RegionId = r.RegionId)
    GROUP BY r.RegionId, r.RegionName
    ORDER BY r.RegionId;
END;

Try a good list, a list with an unknown but valid ID, and three bad inputs.

EXEC dbo.RegionReport @RegionIds = N'1, 2, 2, 3';
EXEC dbo.RegionReport @RegionIds = N'1, 9';

DECLARE @Bad TABLE (n int IDENTITY, Input nvarchar(50));
INSERT @Bad (Input) VALUES (N'1,,3'), (N'1,x'), (N' ');
DECLARE @Input nvarchar(50), @n int = 1;
WHILE @n <= 3
BEGIN
    SELECT @Input = Input FROM @Bad WHERE n = @n;
    BEGIN TRY
        EXEC dbo.RegionReport @RegionIds = @Input;
    END TRY
    BEGIN CATCH
        SELECT @Input AS Input, ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS Problem;
    END CATCH;
    SET @n += 1;
END;

The first call returns North 150, South 225 and West 300, once each. The second returns only North, because 9 is a valid number but matches no region. Decide whether your report should ignore that quietly or tell the caller. Each bad input raises error 50000 with a message the user can act on.

If your application can send a table-valued parameter, use it. You skip the string splitting entirely. Keep the same rules for duplicates and empty selections.

Clean up

DROP PROCEDURE IF EXISTS dbo.RegionReport;
DROP TABLE IF EXISTS dbo.Sales;
DROP TABLE IF EXISTS dbo.Regions;

Next time a report sends a list, split it, check it, and let EXISTS do the matching.

A selected value is not a SQL fragment, it is data to check.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.