如何编写SQL Server存储过程实现HelpDeskCalls工单新增与更新操作

实现思路 #

• 权限校验优先：首先验证操作人是否有录入权限，无权限直接抛出错误终止执行
• 双场景兼容：支持传入RefID追加已有工单记录，也支持不传RefID自动生成新工单
• 并发安全控制：加锁避免多并发时生成重复的RefID或TaskID
• 自动支持工单重开：不对已关闭工单做插入限制，用户只需提交对应描述即可完成重开操作

存储过程代码 #

CREATE PROCEDURE dbo.InsertHelpDeskCall
    @RefID INT = NULL, -- 传NULL为新增工单，传有效值为追加已有工单记录
    @Description NVARCHAR(4000),
    @EnteredBy INT,
    @NewRefID INT OUTPUT, -- 返回新增/追加的工单ID
    @NewTaskID TINYINT OUTPUT -- 返回新生成的任务序号
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    -- 1. 校验用户操作权限
    IF NOT EXISTS (SELECT 1 FROM dbo.HelpDeskUsers WHERE Users = @EnteredBy AND AllowedToLog = 1)
    BEGIN
        RAISERROR('当前用户无录入权限', 16, 1);
        RETURN;
    END;

    BEGIN TRANSACTION;
    BEGIN TRY
        -- 2. 处理新增工单场景
        IF @RefID IS NULL
        BEGIN
            -- 加锁防止并发生成重复RefID
            SELECT @NewRefID = ISNULL(MAX(RefID), 0) + 1 
            FROM dbo.HelpDeskCalls WITH (UPDLOCK, HOLDLOCK);
            
            SET @NewTaskID = 1;
        END
        -- 3. 处理已有工单追加记录场景
        ELSE
        BEGIN
            -- 校验工单是否存在
            IF NOT EXISTS (SELECT 1 FROM dbo.HelpDeskCalls WHERE RefID = @RefID)
            BEGIN
                RAISERROR('指定的工单ID不存在', 16, 1);
                ROLLBACK TRANSACTION;
                RETURN;
            END;

            -- 生成当前工单下递增的TaskID，加锁防并发重复
            SELECT @NewTaskID = ISNULL(MAX(TaskID), 0) + 1 
            FROM dbo.HelpDeskCalls WITH (UPDLOCK, HOLDLOCK)
            WHERE RefID = @RefID;

            SET @NewRefID = @RefID;
        END;

        -- 4. 插入新记录
        INSERT INTO dbo.HelpDeskCalls (RefID, TaskID, Description, EnteredBy)
        VALUES (@NewRefID, @NewTaskID, @Description, @EnteredBy);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        -- 抛出原始错误信息
        THROW;
    END CATCH
END;
GO

调用示例 #

• 示例1：新增工单

DECLARE @Ref INT, @Task TINYINT;
EXEC dbo.InsertHelpDeskCall 
    @Description = '键盘失灵无法输入',
    @EnteredBy = 3,
    @NewRefID = @Ref OUTPUT,
    @NewTaskID = @Task OUTPUT;
SELECT @Ref AS 新工单ID, @Task AS 任务序号;

• 示例2：给已关闭工单追加记录（重开工单）

DECLARE @Ref INT, @Task TINYINT;
EXEC dbo.InsertHelpDeskCall 
    @RefID = 1, -- 已关闭的工单ID
    @Description = '对处理结果不满意，PC频繁蓝屏问题仍存在，申请重新处理',
    @EnteredBy = 1,
    @NewRefID = @Ref OUTPUT,
    @NewTaskID = @Task OUTPUT;
SELECT @Ref AS 工单ID, @Task AS 新任务序号;

• 示例3：无权限用户调用（会抛出错误）

DECLARE @Ref INT, @Task TINYINT;
EXEC dbo.InsertHelpDeskCall 
    @Description = '测试无权限操作',
    @EnteredBy = 5, -- Berry Andrews的AllowedToLog为0
    @NewRefID = @Ref OUTPUT,
    @NewTaskID = @Task OUTPUT;

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