SSRS报表存储过程动态SQL变量处理问题求助：参数执行报错

问题背景 #

SSRS报表团队传入的参数为nvarchar(4000)类型，但目标表字段是varchar(4000)，需在存储过程中转换后执行查询。当前动态SQL拼接时出现两个核心问题：

• string_split中的@var2未被识别为字符串，拆分逻辑失效
• 日期参数转换后格式变为Jun 1 2022，不符合查询所需的'2022-01-01'格式

现有代码的核心问题 #

1. 字符串参数未加引号：拼接string_split(@var2, '^')时，@var2作为字符串值未被单引号包裹，导致SQL解析时将其视为标识符而非字符串
2. 日期转换未指定格式：直接cast日期变量会依赖系统默认格式，导致输出格式不符合预期
3. 低效的循环拼接：用while循环拼接字符串既冗余又低效
4. 非参数化动态SQL：直接拼接变量容易引发格式问题和SQL注入风险

修正方案 #

1. 替换循环拼接为STRING_AGG #

无需手动循环，直接用STRING_AGG快速拼接#slicer中的值（适用于SQL Server 2017+）：

IF @DetailQueryTextParameter1 IS NOT NULL
BEGIN
    SELECT value INTO #slicer FROM STRING_SPLIT(CAST(@DetailQueryTextParameter1 AS varchar(4000)), '^')
    SELECT @var2 = STRING_AGG(value, '^') FROM #slicer
END

2. 参数化动态SQL（推荐方案） #

使用sp_executesql的参数传递功能，避免直接拼接变量，彻底解决格式和注入问题：

• 定义动态SQL模板时使用占位符（如@p_DateStart, @p_OfficeIDs）
• 传递参数数组给sp_executesql

3. 日期格式强制指定（兼容旧版本/必须拼接场景） #

如果一定要拼接日期字符串，用CONVERT指定固定格式（如120对应yyyy-mm-dd hh:mi:ss），并包裹单引号：

''' + CONVERT(varchar(20), @DateParameter1, 120) + '''

完整修正后的存储过程代码 #

CREATE PROCEDURE YourProcedureName
    @TextParameter1 nvarchar(4000) = NULL,
    @DateParameter1 datetime = NULL,
    @DateParameter2 datetime = NULL,
    @DetailQuery nvarchar(max) = ''
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @var2 varchar(4000) = NULL
    DECLARE @slice_w nvarchar(max) = ''

    -- 处理OfficeID参数：转换为varchar并拼接
    IF @TextParameter1 IS NOT NULL
    BEGIN
        SELECT value INTO #slicer FROM STRING_SPLIT(CAST(@TextParameter1 AS varchar(4000)), '^')
        SELECT @var2 = STRING_AGG(value, '^') FROM #slicer
        DROP TABLE #slicer
    END

    -- 构建带占位符的动态SQL条件
    SET @slice_w = N' WHERE (SubmitDate BETWEEN @p_DateStart AND @p_DateEnd)'
    IF @var2 IS NOT NULL
    BEGIN
        SET @slice_w += N' AND (officeid IN (SELECT value FROM string_split(@p_OfficeIDs, ''^'')))'
    END

    -- 拼接完整查询
    SET @DetailQuery += @slice_w

    -- 参数化执行动态SQL
    EXEC sp_executesql 
        @DetailQuery,
        N'@p_DateStart datetime, @p_DateEnd datetime, @p_OfficeIDs varchar(4000)',
        @p_DateStart = @DateParameter1,
        @p_DateEnd = @DateParameter2,
        @p_OfficeIDs = @var2
END

调用示例 #

EXEC YourProcedureName
    @TextParameter1 = N'1o1o1o1o-1o1o10-1p1p1p6-4r5t5y-q2w3er5^5d4f6t21-5f2sde65rf47-f5df6ffd5-d5e8r7',
    @DateParameter1 = '2022-01-01',
    @DateParameter2 = '2022-01-02',
    @DetailQuery = 'SELECT officeid AS Value1, officename AS Value2, claimcode AS Value3 FROM reporting.vStatus '

关键说明 #

• 参数化执行是最优解：既保证变量格式正确，又避免SQL注入风险
• 若使用SQL Server 2016及以下版本，需保留循环拼接，但拼接后要给@var2加单引号（如''' + @var2 + '''）
• 日期参数通过sp_executesql直接传递datetime类型，无需转换为字符串，彻底避免格式问题

内容的提问来源于stack exchange，提问作者user8675309