如何在SQL Server 2012中防止库存转移时出现0或负库存？

问题背景 #

需要实现库房间库存转移功能，核心要求：

• 当源库位无库存（或库存不足）时，阻止转移并提示「没有可转移的设备」
• 绝对禁止库存数量变为负数

当前使用的存储过程仅判断了转移参数的非空性，未验证源库位实际库存，导致执行转移时源库位数量会被更新为负数，无法满足需求。

问题根源 #

现有存储过程的缺陷：

1. 仅检查源库位是否存在，未验证源库位实际库存是否大于等于要转移的数量
2. 未使用事务包裹两个更新操作，可能出现其中一个更新成功、另一个失败的不一致情况
3. 错误提示逻辑不完善，无法区分参数无效、库存不足等不同异常场景

解决方案 #

修改存储过程，加入库存校验、事务控制和完善的错误处理逻辑，确保转移操作的安全性和原子性。

修改后的存储过程代码 #

CREATE PROCEDURE Deneme4
(
    @StockID NVARCHAR(100) = NULL,    
    @FROM NVARCHAR(60) = NULL,
    @TO NVARCHAR(60) = NULL,
    @CNT INTEGER = NULL
)
AS
BEGIN
    SET NOCOUNT ON; -- 关闭影响行数输出，避免干扰提示信息

    DECLARE @SourceStock INT;
    DECLARE @ErrorMessage NVARCHAR(200);

    -- 1. 先做参数合法性校验
    IF @CNT <= 0 OR @StockID IS NULL OR @FROM IS NULL OR @TO IS NULL OR @FROM = '' OR @TO = ''
    BEGIN
        SET @ErrorMessage = '参数无效：转移数量必须大于0，库存ID、源/目标库位不能为空';
        RAISERROR(@ErrorMessage, 16, 1);
        RETURN;
    END

    -- 2. 查询源库位的当前库存
    SELECT @SourceStock = ADET
    FROM STOK_TABLO 
    WHERE URUNID = @StockID AND LOKASYONID = @FROM;

    -- 3. 校验源库位状态
    IF @SourceStock IS NULL
    BEGIN
        SET @ErrorMessage = '源库位不存在对应库存';
        RAISERROR(@ErrorMessage, 16, 1);
        RETURN;
    END

    IF @SourceStock < @CNT
    BEGIN
        RAISERROR('没有可转移的设备', 16, 1);
        RETURN;
    END

    -- 4. 开启事务执行原子性转移操作
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 减少源库位库存
        UPDATE STOK_TABLO 
        SET ADET = ADET - @CNT 
        WHERE URUNID = @StockID AND LOKASYONID = @FROM;

        -- 增加目标库位库存，若目标库位无记录则自动插入（可根据业务需求删除此逻辑）
        UPDATE STOK_TABLO 
        SET ADET = ADET + @CNT 
        WHERE URUNID = @StockID AND LOKASYONID = @TO;

        IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO STOK_TABLO (URUNID, LOKASYONID, ADET, MODELID)
            VALUES (@StockID, @TO, @CNT, @StockID); -- MODELID根据实际表结构调整
        END

        COMMIT TRANSACTION;
        PRINT '库存转移成功';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @ErrorMessage = '转移失败：' + ERROR_MESSAGE();
        RAISERROR(@ErrorMessage, 16, 1);
    END CATCH
END

关键逻辑说明 #

• 参数校验：提前过滤无效参数，避免无意义的数据库操作
• 库存校验：确保源库位存在且库存充足，从根源阻止负数库存的产生
• 事务控制：两个更新操作要么全部成功，要么全部回滚，避免数据不一致
• 异常捕获：捕获执行过程中的错误，回滚事务并返回明确的错误信息
• 可选的目标库位插入：如果业务允许目标库位为空时自动创建库存记录，可保留此逻辑；否则可删除

测试验证 #

以你的测试数据为例：
当尝试从CCR库位（库存0）转移5台DS6878HD时，存储过程会检测到@SourceStock=0 < 5，触发提示「没有可转移的设备」，不会执行任何更新操作，彻底避免源库位出现负数。

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