注册新班次时存储过程Turno_reg无法捕获主键shift_id的问题

问题本质 #

你遇到的核心问题是动态SQL的作用域隔离：SCOPE_IDENTITY()仅能返回当前执行作用域内生成的自增ID，而通过EXEC执行的跨库动态SQL是独立的执行上下文，当前存储过程的作用域无法捕获目标数据库Turno表生成的自增ID，因此获取到的ID始终为NULL。

解决方案 #

要解决这个问题，必须在动态SQL内部直接获取并返回自增ID，再在存储过程中捕获该返回值。以下是经过优化的可靠实现：

ALTER PROC [dbo].[Turno_reg]
(
    @empresa_codigo varchar(11),
    @turno_nombre varchar(25),
    @turno_descripcion varchar(50),
    @turno_tipo char(1),
    @turno_inicio time(0),
    @turno_fin time (0),
    @turno_refrigerio_inicio time (0),
    @turno_refrigerio_fin time (0),
    @turno_status char(1),
    @horario_id bigint,
    @dias_repetidos VARCHAR(MAX) -- 存储班次适用日期的参数（例如："1,3,5"代表周一、周三、周五）
)
AS
BEGIN
    -- 检查必填参数完整性
    IF COALESCE(@turno_nombre, '') = '' 
        OR COALESCE(@turno_tipo, '') = '' 
        OR COALESCE(@turno_inicio, '') = '' 
        OR COALESCE(@turno_fin, '')= '' 
        OR COALESCE(@turno_status, '') = '' 
        OR COALESCE(@horario_id, '') = '' 
    BEGIN
        SELECT Rpta = 1, Msg = 'ERROR! DATOS INCOMPLETOS'
        RETURN
    END

    DECLARE @bdEmpresa varchar(20)
    DECLARE @turno_id BIGINT -- 存储捕获的自增主键

    -- 获取目标企业数据库名称
    SELECT  @bdEmpresa = e.empresa_basededatos
    FROM Empresas e 
    WHERE e.empresa_codigo = LTRIM(RTRIM(@empresa_codigo))

    IF ISNULL(@bdEmpresa,'') <> ''
    BEGIN
        -- 创建临时表存储动态SQL返回的主键
        CREATE TABLE #ClavePrimaria (ID BIGINT);

        -- 构造安全的动态SQL：插入后立即返回自增ID
        DECLARE @sql NVARCHAR(MAX) = N'
            INSERT INTO ' + QUOTENAME(@bdEmpresa) + N'.dbo.Turno(
                turno_nombre, 
                turno_descripcion, 
                turno_tipo, 
                turno_inicio, 
                turno_fin, 
                turno_refrigerio_inicio, 
                turno_refrigerio_fin, 
                turno_status, 
                horario_id
            )
            VALUES(
                ''' + REPLACE(@turno_nombre, '''', '''''') + N''',
                ''' + REPLACE(@turno_descripcion, '''', '''''') + N''',
                ''' + @turno_tipo + N''',
                ''' + CONVERT(NVARCHAR(8), @turno_inicio) + N''',
                ''' + CONVERT(NVARCHAR(8), @turno_fin) + N''',
                ''' + CONVERT(NVARCHAR(8), @turno_refrigerio_inicio) + N''',
                ''' + CONVERT(NVARCHAR(8), @turno_refrigerio_fin) + N''',
                ''' + @turno_status + N''',
                ' + CONVERT(NVARCHAR(20), @horario_id) + N'
            );
            SELECT SCOPE_IDENTITY(); -- 在插入操作的同一作用域获取自增ID
        ';

        -- 执行动态SQL并捕获返回的ID
        INSERT INTO #ClavePrimaria (ID)
        EXEC sp_executesql @sql;

        -- 提取捕获到的主键
        SELECT @turno_id = ID FROM #ClavePrimaria;

        -- 返回统一格式的结果
        SELECT Rpta = 0, Msg = 'Turno registrado correctamente', turno_id = @turno_id;

        DROP TABLE #ClavePrimaria;
    END
    ELSE BEGIN
        SELECT Rpta = 1, Msg = 'LOS DATOS DE LA EMPRESA SON INCORRECTOS'
    END
END

关键优化点 #

1. 作用域内获取ID：在动态SQL内部执行SELECT SCOPE_IDENTITY()，确保在插入操作的同一上下文获取自增主键
2. 安全防护：用QUOTENAME()包裹数据库名、REPLACE()处理字符串中的单引号，避免SQL注入风险
3. 结果统一：返回包含成功状态、提示消息和主键的结构化结果，便于调用方处理
4. 类型匹配：根据实际主键类型调整变量和临时表的字段类型（示例中用BIGINT适配常见自增主键）

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