如何用同一SQL存储过程实现INSERT或UPDATE操作？

问题根源 #

你遇到的错误是因为UPDATE分支未对输出参数@id赋值：当执行UPDATE操作时，@id未被设置有效值，Dapper尝试读取该参数时得到DBNull，但C#中ticket.id是不可空的int类型，类型转换失败抛出异常。

解决方案1：完善分支逻辑，确保UPDATE分支赋值@id #

直接在UPDATE块中添加语句，将现有工单的id赋值给输出参数：

ALTER PROCEDURE [dbo].[spTickets_InsertUpdate]
    @TicketNumber nchar(10),
    @StraightTime money,
    @OverTime money,
    @AdditionalPay money,
    @id int = 0 OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM Tickets WHERE TicketNumber = @TicketNumber)
    BEGIN
        UPDATE dbo.Tickets
        SET AdditionalPay = @AdditionalPay
        WHERE TicketNumber = @TicketNumber;
        
        -- 新增：获取现有工单的id赋值给输出参数
        SELECT @id = id FROM Tickets WHERE TicketNumber = @TicketNumber;
    END
    ELSE
    BEGIN
        INSERT INTO dbo.Tickets (TicketNumber, StraightTime, OverTime, AdditionalPay)
        VALUES (@TicketNumber, @StraightTime, @OverTime, @AdditionalPay);

        SELECT @id = SCOPE_IDENTITY();
    END
END

这样无论执行INSERT还是UPDATE，@id都会被赋值，Dapper读取时不会遇到DBNull，类型转换正常。

解决方案2：使用MERGE语句简化逻辑（推荐） #

MERGE语句可一次性完成判断、插入和更新操作，通过OUTPUT子句统一获取id，逻辑更简洁且避免分支遗漏：

ALTER PROCEDURE [dbo].[spTickets_InsertUpdate]
    @TicketNumber nchar(10),
    @StraightTime money,
    @OverTime money,
    @AdditionalPay money,
    @id int = 0 OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    MERGE INTO dbo.Tickets AS Target
    USING (VALUES (@TicketNumber, @StraightTime, @OverTime, @AdditionalPay)) 
        AS Source (TicketNumber, StraightTime, OverTime, AdditionalPay)
    ON Target.TicketNumber = Source.TicketNumber
    WHEN MATCHED THEN
        UPDATE SET Target.AdditionalPay = Source.AdditionalPay
    WHEN NOT MATCHED THEN
        INSERT (TicketNumber, StraightTime, OverTime, AdditionalPay)
        VALUES (Source.TicketNumber, Source.StraightTime, Source.OverTime, Source.AdditionalPay)
    OUTPUT inserted.id INTO @id; -- 统一获取插入或更新后的id
END

额外优化建议 #

为避免重复工单，建议给TicketNumber添加唯一约束：

ALTER TABLE dbo.Tickets ADD CONSTRAINT UQ_Tickets_TicketNumber UNIQUE (TicketNumber);

C#代码无需修改 #

现有C#代码可直接调用修改后的存储过程，@id参数始终会被赋值，Dapper能正常将其转换为int类型赋值给ticket.id。

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