带多参数的SQL存储过程子查询执行报错的最优修复方案求助

• 错误1：动态SQL执行逻辑错误：exec(@qry+@TotalRecords) 将统计总记录数的子查询直接拼在主查询语句末尾，导致出现一段无上下文的独立COUNT查询，触发「必须指定要查询的表」报错。
• 错误2：总记录数统计逻辑错误：@TotalRecords 对应的子查询加了group by pl.Name，返回的是每个分组的行数（多个值），但该子查询被放在SELECT列表中作为字段使用，触发「选择列表中仅可指定一个表达式」报错。
• 错误3：查询数据源逻辑错误：带行号、分组金额的子查询被错误放在SELECT列表中，而不是作为FROM子句的数据源，导致后续WHERE条件找不到RowNo、Name等字段，触发列名无效报错。

调整动态SQL拼接逻辑，同时优化性能、避免SQL注入风险，修复后的完整存储过程如下：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PA_Report_IndexPagingData]
    @PageSize int,
    @PageIndex int,
    @startdt1 nvarchar(50),  
    @startdt2 nvarchar(50),
    @Sort nvarchar(50),
    @Search nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @qry nvarchar(max)
    -- 提前转换日期参数，避免重复转换，同时兼容非法日期输入
    DECLARE @StartDate DATETIME = TRY_CONVERT(DATETIME,@startdt1,110)
    DECLARE @EndDate DATETIME = TRY_CONVERT(DATETIME,@startdt2,110)

    -- 提前统计符合条件的总分组数，避免主查询重复执行关联查询
    DECLARE @TotalRecords INT
    SELECT @TotalRecords = COUNT(DISTINCT pl.Name)
    FROM PA_Ledger pl 
    JOIN PA_Trans pt ON (pl.LedgerId = pt.Ledgeridcr OR pl.LedgerId = pt.Ledgeriddr ) 
    WHERE ISNULL(Isdeleted,0) = 0 
        AND pl.Name LIKE '%'+@Search+'%' 
        AND (@StartDate IS NULL OR pt.TransOn >= @StartDate OR CONVERT(DATE,@StartDate) = '1900-01-01')
        AND (@EndDate IS NULL OR pt.TransOn <= @EndDate OR CONVERT(DATE,@EndDate) = '1900-01-01')

    -- 排序规则白名单校验，避免SQL注入
    IF @Sort NOT IN ('Order By Name Asc','Order By Name Desc','Order By Amount Asc','Order By Amount Desc')
        SET @Sort = 'Order By Name Asc'

    -- 主查询：分页逻辑
    SET @qry = N'
    SELECT 
        RowNo,
        Name,
        Amount,
        @TotalRecords AS TotalRecords
    FROM (
        SELECT 
            ROW_NUMBER() OVER(ORDER BY pl.name) as RowNo,
            pl.Name,
            SUM(pt.Amount) as Amount
        FROM PA_Ledger pl
        JOIN PA_Trans pt ON (pl.LedgerId = pt.Ledgeridcr OR pl.LedgerId = pt.Ledgeriddr)
        WHERE ISNULL(Isdeleted,0) = 0 
            AND pl.Name LIKE ''%'+@Search+'%''
            AND (@StartDate IS NULL OR pt.TransOn >= @StartDate OR CONVERT(DATE,@StartDate) = ''1900-01-01'')
            AND (@EndDate IS NULL OR pt.TransOn <= @EndDate OR CONVERT(DATE,@EndDate) = ''1900-01-01'')
        GROUP BY pl.Name
    ) AS t
    WHERE RowNo BETWEEN @StartRow AND @EndRow
    ' + @Sort

    -- 计算分页边界
    DECLARE @StartRow INT = (@PageIndex-1)*@PageSize + 1
    DECLARE @EndRow INT = (@PageIndex-1)*@PageSize + @PageSize

    -- 带参数执行动态SQL，彻底避免SQL注入风险
    EXEC sp_executesql @qry,
        N'@TotalRecords INT, @StartDate DATETIME, @EndDate DATETIME, @StartRow INT, @EndRow INT',
        @TotalRecords = @TotalRecords,
        @StartDate = @StartDate,
        @EndDate = @EndDate,
        @StartRow = @StartRow,
        @EndRow = @EndRow
END
GO

• 移除了重复冗余的查询逻辑，总记录数提前统计，避免在主查询中重复执行相同的关联查询，性能提升明显
• 使用sp_executesql带参数执行动态SQL，搭配排序规则白名单校验，彻底避免SQL注入风险
• 提前转换日期参数，增加TRY_CONVERT避免非法日期格式导致报错
• 调整查询结构，将分页子查询作为主查询的数据源，所有字段引用合法

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