开发者

How to merge this `IF ... ELSE`in a Stored-Procedure?

i have a feeling that this SP could be simplified. Is it possible to eliminate that IF...ELSE and merge it with the same WHERE-Condition?

CREATE PROC [dbo].[UpdateRmaModelWithTacList](
    @onlyNoModel bit 
)
AS
IF @onlyNoModel=1 
BEGIN
    UPDATE  RMA
    SET       ModelID = COALESCE(modModel.idModel, 1)
    FROM      tabTAC INNER JOIN
                modModel ON tabTAC.fiModel = modModel.idModel RIGHT OUTER JOIN
                 RMA ON tabTAC.TAC = SUBSTRING(RMA.IMEI, 1, 8)
    WHERE     (RMA.ModelID = 1)
END 
ELSE
BEGIN
    UPDATE RMA
    SET      ModelID = COALESCE(modModel.idModel, RMA.ModelID)
    FROM     tabTAC INNER JOIN
              modModel ON tabTAC.fiModel = modModel.idModel RIGHT OUTER JOIN
  开发者_运维知识库              RMA ON tabTAC.TAC = SUBSTRING(RMA.IMEI, 1, 8)
END

RETURN @@ROWCOUNT;

Go


WHERE (@onlyNoModel != 1 OR RMA.ModelID = 1)

Whether this is as efficient as the IF..ELSE version is up to the query optimizer. The WHERE will hopefully be optimized away by any decent optimizer if @onlyNoModel is not 1.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜