如何从CreateUpdateFeedback存储过程返回创建/更新数据的ID？

解决方案 #

首先假设你表[ExecutiveChange].[RecognitionFeedback]里的自增主键（Identity列）名为FeedbackId，如果实际列名不同，替换代码中对应的列名即可。

修改逻辑如下：

• 新增输出参数接收返回的ID值，也可以选择直接返回结果集，两种方式都提供参考
• 插入操作时用SCOPE_IDENTITY()获取当前作用域刚生成的自增ID，安全性比@@IDENTITY更高，不会受其他会话或触发器的影响
• 更新操作时直接通过匹配条件查询对应记录的自增ID返回即可

完整修改后存储过程代码（带输出参数版本）

CREATE PROCEDURE [ExecutiveChange].[CreateUpdateFeedback] 
    @RecognitionLogId INT,
    @RecognitionStatus VARCHAR(255),
    @FeedbackStatus VARCHAR(255),
    @FeedbackComment VARCHAR(255),
    @FeedbackBy VARCHAR(255),
    @ClarifiedChangeType VARCHAR(255),
    @ClarifiedCompanyName VARCHAR(255),
    @FeedbackApprovedBy VARCHAR(255),
    @FeedbackApprovedDateTime DATETIME,
    -- 新增输出参数，返回自增ID
    @OutputFeedbackId INT OUTPUT
AS
BEGIN
    -- 先判断记录是否存在（比原逻辑查询全量数据效率更高）
    IF EXISTS(SELECT 1 FROM [ExecutiveChange].[RecognitionFeedback] s WHERE s.RecognitionLogId = @RecognitionLogId)
    BEGIN
        -- 执行更新
        UPDATE [ExecutiveChange].[RecognitionFeedback]
        SET RecognitionStatus        = @RecognitionStatus,
            FeedbackStatus           = @FeedbackStatus,
            FeedbackComment          = @FeedbackComment,
            FeedbackBy               = @FeedbackBy,
            FeedbackDateTime         = GETDATE(),
            ClarifiedChangeType      = @ClarifiedChangeType,
            ClarifiedCompanyName     = @ClarifiedCompanyName,
            FeedbackApprovedBy       = @FeedbackApprovedBy,
            FeedbackApprovedDateTime = @FeedbackApprovedDateTime
        WHERE RecognitionLogId = @RecognitionLogId

        -- 查询更新记录的ID赋值给输出参数
        SELECT @OutputFeedbackId = FeedbackId 
        FROM [ExecutiveChange].[RecognitionFeedback] 
        WHERE RecognitionLogId = @RecognitionLogId
    END
    ELSE
    BEGIN
        -- 执行插入
        INSERT INTO [ExecutiveChange].[RecognitionFeedback](
            RecognitionLogId,
            RecognitionStatus,
            FeedbackStatus,
            FeedbackComment,
            FeedbackBy,
            FeedbackDateTime,
            ClarifiedChangeType,
            ClarifiedCompanyName,
            FeedbackApprovedBy,
            FeedbackApprovedDateTime
        )
        VALUES (
            @RecognitionLogId, 
            @RecognitionStatus, 
            @FeedbackStatus, 
            @FeedbackComment, 
            @FeedbackBy, 
            GETDATE(),
            @ClarifiedChangeType, 
            @ClarifiedCompanyName, 
            @FeedbackApprovedBy, 
            @FeedbackApprovedDateTime
        )
        -- 获取刚插入的自增ID
        SET @OutputFeedbackId = SCOPE_IDENTITY()
    END
END
GO

调用示例（输出参数版本）

DECLARE @ReturnId INT
EXEC [ExecutiveChange].[CreateUpdateFeedback] 
    @RecognitionLogId = 1,
    @RecognitionStatus = '待审核',
    @FeedbackStatus = '通过',
    @FeedbackComment = '无问题',
    @FeedbackBy = '张三',
    @ClarifiedChangeType = '高管变动',
    @ClarifiedCompanyName = 'XX科技有限公司',
    @FeedbackApprovedBy = '李四',
    @FeedbackApprovedDateTime = '2024-01-01 12:00:00',
    @OutputFeedbackId = @ReturnId OUTPUT

-- 查看返回的ID
SELECT @ReturnId AS 操作记录ID

如果你不需要输出参数，希望直接返回结果集，只需要去掉新增的@OutputFeedbackId参数，把对应的赋值语句改成SELECT语句即可：

• 更新分支替换成SELECT FeedbackId FROM [ExecutiveChange].[RecognitionFeedback] WHERE RecognitionLogId = @RecognitionLogId
• 插入分支替换成SELECT SCOPE_IDENTITY() AS FeedbackId

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