存储过程无法恢复ANSI参数原设置问题排查

我想要保存当前会话的ANSI参数设置，执行一些修改参数的任务后，再从全局临时表恢复到原始设置。为此我创建了两个存储过程SaveAnsi和BringBackAnsi：

CREATE PROCEDURE SaveAnsi
AS
    BEGIN

        DECLARE @DynamicSQL NVARCHAR(1000);
        DECLARE @Insert NVARCHAR(1000);
        DECLARE @I INT;
        DECLARE @table VARCHAR(30);
        SELECT
            @I = @@SPID; --用SPID命名全局临时表
        SET @table = '##AnsiSet' + CAST(@I AS VARCHAR(10));
        SET @DynamicSQL = N'CREATE TABLE ' + QUOTENAME(@table) + N'(NAME varchar(50),STATUS bit)';
        EXEC sp_executesql
            @DynamicSQL;
        SELECT
             *
        INTO #Ansi
        FROM
             (
                 SELECT
                     name,
                     [Status] = CASE
                                    WHEN (@@OPTIONS & number) = 0
                                        THEN
                                        '0'
                                    ELSE
                                        '1'
                                END
                 FROM
                     master.dbo.spt_values
                 WHERE
                     type = 'SOP'
                     AND number > 0
             ) AS X;

        SET @Insert = 'INSERT INTO ' + QUOTENAME(@table) + ' SELECT * FROM #Ansi';
        EXEC sp_executesql @Insert;

    END;

create PROCEDURE dbo.[BringBackAnsi] @SPID VARCHAR(10)
AS
    BEGIN
        DECLARE @Tabel VARCHAR(30);
        DECLARE @Select NVARCHAR(1000);
        DECLARE @Setting NVARCHAR(1000);
        SET @Select = N'';
        SET @Setting = N'';
        SET @Tabel = '##AnsiSet' + @SPID;
        CREATE TABLE #Temp
            (
                Con INT IDENTITY(1, 1),
                Name  VARCHAR(30),
                Status INT
            );

        SET @Select = N'insert into #Temp(Name,Status) select Name,Status from ' + @Tabel;
        EXEC sp_executesql @Select;
        DECLARE @b INT;
        DECLARE @bmax INT;
        DECLARE @Status INT;
        DECLARE @Name VARCHAR(30);
        SET @b = 2;
        SET @Name = '';
        SELECT
            @bmax = MAX(Con)
        FROM
            #Temp;
        WHILE @b <= @bmax
            BEGIN
                SELECT
                    @Name  = Name,
                    @Status = Status
                FROM
                    #Temp
                WHERE
                    Con = @b;
                IF @Status = 1
                    SET @Setting = N'set ' + @Name + N' ON;';
                ELSE
                    SET @Setting = N'set ' + @Name + N' OFF;';
        EXEC sp_executesql @Setting;
                PRINT @Setting;
                SET @b = @b + 1;
            END;
        DROP TABLE #Temp;
    END;

操作步骤：

• 调用EXEC SaveAnsi保存原始设置
• 修改参数：SET NOCOUNT ON（原本为OFF）
• 查询@@SPID获取会话ID（示例为543）
• 调用EXEC BringBackAnsi 543尝试恢复原始设置

执行后通过以下语句检查设置，发现并未恢复为原始状态：

SELECT  *
FROM
(
    SELECT  name,
        [Status] = CASE WHEN (@@OPTIONS & number) = 0 THEN '0' ELSE '1' END
    FROM    master.dbo.spt_values 
    WHERE       type='SOP'
        AND number > 0
) AS X

调试发现设置仅在存储过程内部生效，外部会话的参数并未恢复。

核心问题 #

存储过程中通过sp_executesql执行的SET语句，作用域仅限于sp_executesql自身的执行上下文，存储过程执行完毕后，这些设置不会传递到调用它的外层会话。另外原代码从@b=2开始循环，会漏掉第一个ANSI参数。

修正方案 #

将所有SET语句拼接成一个完整的批处理字符串，在存储过程的顶层作用域执行，确保设置直接作用于当前调用会话。同时修复参数遗漏问题，优化临时表创建逻辑。

修正后的SaveAnsi存储过程

ALTER PROCEDURE SaveAnsi
AS
BEGIN
    DECLARE @DynamicSQL NVARCHAR(1000);
    DECLARE @Insert NVARCHAR(1000);
    DECLARE @I INT;
    DECLARE @table VARCHAR(30);
    SELECT @I = @@SPID;
    SET @table = '##AnsiSet' + CAST(@I AS VARCHAR(10));

    -- 先删除已存在的同名全局临时表，避免重复创建报错
    SET @DynamicSQL = N'IF OBJECT_ID(''tempdb..' + QUOTENAME(@table) + ''') IS NOT NULL DROP TABLE ' + QUOTENAME(@table);
    EXEC sp_executesql @DynamicSQL;

    -- 用INT类型存储Status，避免字符串转BIT的潜在问题
    SET @DynamicSQL = N'CREATE TABLE ' + QUOTENAME(@table) + N'(NAME varchar(50),STATUS INT)';
    EXEC sp_executesql @DynamicSQL;

    SELECT name, [Status] = CASE WHEN (@@OPTIONS & number) = 0 THEN 0 ELSE 1 END
    INTO #Ansi
    FROM master.dbo.spt_values
    WHERE type = 'SOP' AND number > 0;

    SET @Insert = 'INSERT INTO ' + QUOTENAME(@table) + ' SELECT * FROM #Ansi';
    EXEC sp_executesql @Insert;
END;

修正后的BringBackAnsi存储过程

ALTER PROCEDURE dbo.[BringBackAnsi] @SPID VARCHAR(10)
AS
BEGIN
    DECLARE @Table VARCHAR(30);
    DECLARE @FullSetting NVARCHAR(MAX); -- 改用MAX避免长度限制
    SET @Table = '##AnsiSet' + @SPID;

    -- 拼接所有SET语句为一个完整批处理
    SELECT @FullSetting = COALESCE(@FullSetting + CHAR(10), '') 
                          + 'SET ' + Name + ' ' + CASE WHEN Status = 1 THEN 'ON' ELSE 'OFF' END + ';'
    FROM 
        (SELECT Name, Status FROM ' + @Table + ') AS AnsiSettings;

    -- 执行完整批处理，确保作用于当前会话
    IF @FullSetting IS NOT NULL
        EXEC sp_executesql @FullSetting;

    -- 可选：打印执行的语句用于调试
    PRINT @FullSetting;
END;

关键说明 #

1. 作用域控制：将所有SET语句合并为一个批处理执行，确保设置直接作用于调用存储过程的当前会话，而非内部子上下文。
2. 参数完整性：通过SELECT直接拼接所有参数的设置语句，避免循环起始值错误导致的参数遗漏。
3. 容错性优化：SaveAnsi中加入了删除已有临时表的逻辑，避免重复调用时报错。

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