SQL Server 2008 R2动态存储过程无法为@RecordCount输出参数赋值

嘿，我仔细看了你的sp_getAssetListAudit存储过程，没法给@RecordCount赋值、拿不到结果集的核心问题是动态SQL里输出参数的传递方式错误，另外还有参数类型不合理、存在SQL注入风险这些小坑。下面是修正后的完整代码，以及我整理的关键问题说明：

修正后的存储过程代码 #

ALTER PROCEDURE [dbo].[sp_getAssetListAudit] 
    @type nvarchar(20), 
    @typeid nvarchar(5), 
    @clientId nvarchar(5), 
    @PageIndex int,  -- 把分页相关参数改成int类型，更贴合业务场景，避免字符串转换问题
    @PageSize int,   -- 同上
    @RecordCount int output  -- 记录数是整数，用int类型更合理
AS 
BEGIN 
    SET NOCOUNT ON; 
    DECLARE @SQL nvarchar(max) 
    DECLARE @ParamDefinition nvarchar(max)  -- 用来定义动态SQL的参数映射关系

    -- 先构建基础查询的SQL语句，把固定部分写好
    SET @SQL = N'
        SELECT 
            ROW_NUMBER() OVER ( ORDER BY ad.arid ASC ) AS rownum, 
            ad.arid,ad.ast_code,ad.ast_descp,
            ISNULL(cat.name,'''') AS cat,
            ISNULL(loc.name,'''') AS loc,
            ISNULL(gp.name,'''') AS grp, 
            ISNULL(cc.name,'''') AS cc, 
            ad.ast_qty AS qty 
        INTO #Results 
        FROM tbl_AssetDetails ad 
        LEFT JOIN tbl_Category cat ON ad.ast_cat = cat.catid 
        LEFT JOIN tbl_Subcategory scat ON ad.ast_subcat = scat.subcatid 
        LEFT JOIN tbl_Location loc ON loc.lid = ad.ast_loc 
        LEFT JOIN tbl_Group gp ON gp.gid = ad.ast_grp 
        LEFT JOIN tbl_CostCenter cc ON cc.ccid = ad.ast_costcen 
        WHERE ad.ast_status NOT IN (3,-1) 
          AND ad.clientId = @clientId ';

    -- 根据@type拼接对应的过滤条件，这里用参数传递而不是直接拼接字符串，防止SQL注入
    IF(@type='cat') 
        SET @SQL += N' AND ad.ast_cat = @typeid ';
    IF(@type='subcat') 
        SET @SQL += N' AND ad.ast_subcat = @typeid ';
    IF(@type='loc') 
        SET @SQL += N' AND ad.ast_loc = @typeid ';
    IF(@type='grp') 
        SET @SQL += N' AND ad.ast_grp = @typeid ';
    IF(@type='cc') 
        SET @SQL += N' AND ad.ast_costcen = @typeid ';
    IF(@type='ast') 
        SET @SQL += N' AND ad.arid = @typeid ';

    -- 给输出参数@RecordCount赋值，同时添加分页查询和临时表清理的语句
    SET @SQL += N'
        SELECT @RecordCount = COUNT(*) FROM #Results;
        SELECT * FROM #Results 
        WHERE rownum BETWEEN (@PageIndex -1) * @PageSize + 1 
                        AND ((@PageIndex -1) * @PageSize + 1) + @PageSize - 1;
        DROP TABLE #Results;';

    -- 定义动态SQL需要的参数，包括输入和输出参数的类型
    SET @ParamDefinition = N'
        @type nvarchar(20), 
        @typeid nvarchar(5), 
        @clientId nvarchar(5), 
        @PageIndex int, 
        @PageSize int, 
        @RecordCount int OUTPUT';

    -- 执行动态SQL，把外部参数传递进去，同时接收输出参数的值
    EXEC sp_executesql @SQL, @ParamDefinition,
        @type = @type,
        @typeid = @typeid,
        @clientId = @clientId,
        @PageIndex = @PageIndex,
        @PageSize = @PageSize,
        @RecordCount = @RecordCount OUTPUT;
END

关键问题说明 #

• 输出参数传递错误：你原来直接把@RecordCount拼接进动态SQL里，这完全搞错了输出参数的用法。正确的做法是在sp_executesql的参数定义里声明它是输出参数，然后在动态SQL内部给这个参数赋值，最后通过OUTPUT关键字把值传递回存储过程的外部变量。
• 参数类型优化：@PageIndex、@PageSize、@RecordCount本来就是整数类型，你之前用nvarchar(5)不仅容易出现转换错误，还不符合数据语义，改成int类型更合理。
• SQL注入风险修复：原来的代码直接把变量拼接到SQL语句里，要是有人恶意构造参数，很容易就会触发SQL注入攻击。现在改成参数化查询，所有变量都通过sp_executesql的参数传递，彻底规避了这个风险。
• 代码可读性提升：我把SQL语句拆成了多行，加了适当的缩进，这样以后维护起来也更方便。

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