单个if语句需要在代码块中加上Begin和End。

Question

我之前遇到过这个问题，不知道为什么需要“Begin”和“End”才能得到正确的值。如果语句是单个if，则不需要“Begin”和“End”，但如果有多个语句，则需要它。如果省略，则在创建/修改过程时会生成执行错误。

有任何关于为什么在MS SQL中出现这种行为的想法吗？

谢谢，Craig

-- 结果集2返回正确的值。

SQL.

 Declare @Qty DECIMAL(10,2), @UOM VARCHAR(5), @CasePack Numeric(7,1), @CaseQty Numeric(11, 4), @im_weigh_item SmallInt, @rank_wi_ven_ctg Char(1), @po_qty_uom Char(1), @po_Qty float

 Select 
  -- these 2 Params are Const in this process
  @im_weigh_item =0, @rank_wi_ven_ctg = 'C', 
  -- Set Values
  @UOM = 'C' , @po_Qty_uom = 'M', @po_Qty = 3,  @casepack =6, @Qty = 2

 /*
  Check and Set vars. accordingly
  This Conditional Block Generates no errors, but the results are incorrect
  ** NO "Begin" & End"
 */
 If(@im_weigh_item=1)
  If(@rank_wi_ven_ctg='U')
   Select @UOM = 'U'
 Else
   If(@po_Qty_uom != 'C' )
   If(@po_Qty!=@casepack)
    Select @UOM = 'U', @Qty = @Qty * @po_Qty
 -- Debug 
 Select @po_Qty_uom as po_Qty_uom, @UOM as UOM, @casepack as casepack, @po_Qty as po_Qty, @Qty as Qty
 -- Debug 

 -- reset  vars, test 2
 Select @UOM = 'C' , @po_Qty_uom = 'M', @po_Qty = 3,  @casepack =6, @Qty =2

 /*
  *** Works *** Calcs Correctly
  Check and Set vars. accordingly
  *** This Block uses the "Begin" & "End"
 */
 If(@im_weigh_item=1)
 begin
  If(@rank_wi_ven_ctg='U')
   Select @UOM = 'U'
 end
 Else
 begin
   If(@po_Qty_uom != 'C' )
   If(@po_Qty!=@casepack)
    Select @UOM = 'U', @Qty = @Qty * @po_Qty
 end

 -- Debug 
 Select @po_Qty_uom as po_Qty_uom, @UOM as UOM, @casepack as casepack, @po_Qty as po_Qty, @Qty as Qty
 -- Debug 

- Craig

Answer 1

ELSE语句总是绑定到最近的前置IF而不带ELSE，因此您的

If(@im_weigh_item=1)
  If(@rank_wi_ven_ctg='U')
    Select @UOM = 'U'
Else
  If(@po_Qty_uom != 'C' )
  If(@po_Qty!=@casepack)
    Select @UOM = 'U', @Qty = @Qty * @po_Qty

被解释为：

If(@im_weigh_item=1) begin
  If(@rank_wi_ven_ctg='U') begin
    Select @UOM = 'U'
  end
  Else begin
    If(@po_Qty_uom != 'C' ) begin
      If(@po_Qty!=@casepack) begin
        Select @UOM = 'U', @Qty = @Qty * @po_Qty
      end
    end
  end
end

- Tomek Szpakowicz

Answer 2

我认为没有BEGIN和END的IF语句应该只包含一个指令。我建议在每个IF语句中添加BEGIN .. END，以帮助保持你的编码一致性。

- munissor

Answer 3

在你的错误版本中添加显式的BEGIN和END会产生以下结果，它的逻辑与你的正常版本不同：

If(@im_weigh_item=1)
BEGIN
    If(@rank_wi_ven_ctg='U')
    BEGIN
        Select @UOM = 'U'
    END
    Else  -- this "else" is associated with the wrong  "if"
    BEGIN
        If(@po_Qty_uom != 'C' )
        BEGIN
            If(@po_Qty!=@casepack)
            BEGIN
                Select @UOM = 'U', @Qty = @Qty * @po_Qty
            END
        END
    END
END

- LukeH