用于各种SQL更新的case表达式

Question

我正在努力使用case表达式来确定要执行哪条update语句。下面的是可能的吗?或者我应该为每个更新创建单独的更新存储过程吗？

 IF (SELECT pick FROM warehouse WHERE order_no = @order_no and pick = @pick) is null
    CASE @pick 
    when 1 then
            UPDATE warehouse
            SET pick = @pick, startpickdate=@dchar, startpicktime=@tchar
            where order_no=@order_no
    when 2 then
            UPDATE warehouse
            SET pick = @pick, endpickdate=@dchar, endpicktime=@tchar
            where order_no=@order_no
    when 0 then
            UPDATE warehouse
            SET pick = @pick, endpickdate='', endpicktime='',startpickdate='', startpicktime=''
            where order_no=@order_no
END
GO

Answer 1

如果您希望有条件地更新列，则此方法将适用于您

UPDATE Warehouse
SET Pick = @pick
   , startPickDate = CASE @pick
                        WHEN 1 THEN @dchar
                        WHEN 2 THEN startPickDate
                        WHEN 0 THEN ''
                     END
   , startPickTime = CASE @pick
                        WHEN 1 THEN @tchar
                        WHEN 2 THEN startPickTime
                        WHEN 0 THEN ''
                     END
   , ...
WHERE order_no = @order_no

Answer 2

T-SQL control-of-flow不使用CASE。因此，一种方法是继续使用IF (实际的流控制)，这将使您的update语句更简单，尽管每个分支都有一个：

IF (SELECT pick FROM warehouse WHERE order_no = @order_no AND pick = @pick) IS NULL BEGIN
    IF @pick = 1 BEGIN
        UPDATE warehouse
        SET pick = @pick, startpickdate=@dchar, startpicktime=@tchar
        WHERE order_no=@order_no
    END
    ELSE IF @pick = 2 BEGIN
        UPDATE warehouse
        SET pick = @pick, endpickdate=@dchar, endpicktime=@tchar
        WHERE order_no=@order_no
    END
    ELSE IF @pick = 0 BEGIN
        UPDATE warehouse
        SET pick = @pick, endpickdate='', endpicktime='',startpickdate='', startpicktime=''
        WHERE order_no=@order_no
    END
END
GO

如果你在一个存储进程中，你可能会发现这更容易维护，尽管这当然是主观的。

我可能倾向于使用这种方法，而不是使用case使用一个大型的组合update语句，因为您在每种情况下都更新不同的列。