SQL Server通过exec(@sql)获取动态表已有记录ID存入变量问题

核心实现思路：使用sp_executesql系统存储过程替代直接EXEC执行动态SQL，通过参数绑定的方式实现动态SQL内外变量的值传递，同时解决原有代码的语法问题、SQL注入风险。 #

原有代码核心问题 #

• 字符串类型参数直接拼接未加单引号，会触发SQL语法错误，同时存在极高的SQL注入风险
• 动态SQL与外层存储过程作用域完全隔离，直接EXEC(@SQL)无法将内部查询得到的ID值回传给外层的@ID变量
• 拼接SQL时@USER_ID后遗漏空格，会导致后续AND CONTROLLER与前面的字段值拼接为非法语法
• 未处理NULL入参场景：任意字符串类型入参为NULL时，拼接生成的整条动态SQL都会变为NULL，执行失效
• DECLARE变量块末尾多了多余逗号，本身就存在语法错误

修改后完整代码 #

ALTER PROCEDURE [dbo].[CREATE_ERROR_LOG] 
    @ERROR_MESSAGE VARCHAR(MAX) = NULL,
    @ERROR_CODE INT = 200,
    @USER_ID VARCHAR(50) = '00000',
    @TABLE_NAME VARCHAR(20),
    @CONTROLLER VARCHAR(50) = NULL,
    @METHOD VARCHAR(50) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ID AS INT = 0,
            @SQL AS NVARCHAR(MAX); -- sp_executesql要求SQL语句为NVARCHAR类型

    -- 先校验传入的表名是否合法，避免非法表名报错和注入风险
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @TABLE_NAME AND schema_id = SCHEMA_ID('dbo'))
    BEGIN
        RAISERROR('传入的表名不存在于dbo schema下', 16, 1);
        RETURN;
    END

    -- 构造动态SQL：仅表名需要拼接，其余参数全部通过绑定传入
    SET @SQL = N'
    SELECT @ID_OUT = ID  
    FROM dbo.' + QUOTENAME(@TABLE_NAME) + N' 
    WHERE 
        CODE = @ERROR_CODE_IN
        AND (MESSAGE = @ERROR_MESSAGE_IN OR (MESSAGE IS NULL AND @ERROR_MESSAGE_IN IS NULL))
        AND USER_ID = @USER_ID_IN
        AND (CONTROLLER = @CONTROLLER_IN OR (CONTROLLER IS NULL AND @CONTROLLER_IN IS NULL))
        AND (METHOD = @METHOD_IN OR (METHOD IS NULL AND @METHOD_IN IS NULL))';

    -- 执行动态SQL，通过参数绑定实现值回传
    EXEC sp_executesql 
        -- 要执行的动态SQL语句
        @SQL,
        -- 参数定义块
        N'@ERROR_CODE_IN INT, 
          @ERROR_MESSAGE_IN VARCHAR(MAX), 
          @USER_ID_IN VARCHAR(50), 
          @CONTROLLER_IN VARCHAR(50), 
          @METHOD_IN VARCHAR(50), 
          @ID_OUT INT OUTPUT',
        -- 输入参数赋值
        @ERROR_CODE_IN = @ERROR_CODE,
        @ERROR_MESSAGE_IN = @ERROR_MESSAGE,
        @USER_ID_IN = @USER_ID,
        @CONTROLLER_IN = @CONTROLLER,
        @METHOD_IN = @METHOD,
        -- 输出参数绑定到外层@ID变量
        @ID_OUT = @ID OUTPUT;

    -- 测试输出ID
    PRINT(@ID);

    SELECT @ERROR_CODE AS Code, @ERROR_MESSAGE AS MESSAGE;
END

关键逻辑说明 #

• sp_executesql是SQL Server官方推荐的动态SQL执行方式，支持参数绑定，通过声明OUTPUT类型的参数可以将动态SQL内部的查询结果回传到外层变量，满足获取ID的需求
• 除了必须拼接的表名外，所有查询条件都通过参数传入，不需要手动拼接字符串、加单引号，完全避免SQL注入风险和拼接语法错误
• 用QUOTENAME包裹表名，处理表名含特殊字符的场景，同时降低表名拼接带来的注入风险
• 新增表名校验逻辑，避免传入非法表名导致执行报错
• NULL值匹配逻辑使用(字段 = 参数 OR (字段 IS NULL AND 参数 IS NULL))的写法，严格匹配NULL值场景，如果需求是空字符串和NULL视为相等，可以替换为ISNULL(字段, '') = ISNULL(参数, '')

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