Use an if statement inside a stored procedure query?

Question

I'm trying to convert a ColdFusion query into a stored procedure. My original query is as follows

UPDATE Products
SET Active = 1,
    Pounds = #lbs#,
    Ounces = #ozs#,
    Qty = #Qty#,
    PrevQty = #GetItem.Qty#,
    PrevWMQty = #GetItem.Qty#,
    PrevGoogleQty = #GetItem.Qty#,
    <cfif Cost NEQ GetItem.OurCost>
        PrevOurCost = #GetItem.OurCost#,
    </cfif>
    OurCost = #Cost#
WHERE ItemID = '#fixedItemID#'

As you can see I have IF statements inside my query so if certain conditions are met that it will update those fields. If those conditions are not met then it will not include them in the update.

So I would like something like this but I am not sure this is possible in a stored procedure

CREATE PROCEDURE [dbo].[spUpdateQty] 
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE Products
    SET Active = 1,
        Pounds = @var1,
        Ounces = @var2,
        Qty = @var3,
        PrevQty = @var4,
        PrevWMQty = @var5,
        PrevGoogleQty = @var6,
        IF (@var7 >= @var8)
        BEGIN
            PrevOurCost = @var7,
        END
        OurCost = @var8
    WHERE ItemID = @var9
END

I am basically looking to pass in optional parameters and based on their value either update that field or not. I hate to have to write an individual separate query for each IF statement for every possibility. So is this even possible or am I going about this the wrong way?

Answer 1

You can use a case expression to conditionally update a column, which avoids needing to duplicate your update statement.

e.g.

UPDATE Products SET
    Active = 1,
    Pounds = @var1,
    Ounces = @var2,
    Qty = @var3,
    PrevQty = @var4,
    PrevWMQty = @var5,
    PrevGoogleQty = @var6,
    PrevOurCost = case when @var7 >= @var8 then @var7 else PrevOurCost end,
    OurCost = @var8
WHERE ItemID = @var9

Answer 2

There are more than 1 way to do that

You can use Case Statement

      UPDATE Products
      SET Active = 1,
          Pounds = @var1,
          Ounces = @var2,
          Qty = @var3,
          PrevQty = @var4,
          PrevWMQty = @var5,
          PrevGoogleQty = @var6,
          PrevOurCost = CASE WHEN @var7 >= @var8 THEN @var7 ELSE PrevOurCost END,
          OurCost = @var8
        WHERE ItemID = @var9

or you can use IF statement out side of the UPDATE statement

IF (@var7 >= @var8)
BEGIN

      UPDATE Products
      SET Active = 1,
          Pounds = @var1,
          Ounces = @var2,
          Qty = @var3,
          PrevQty = @var4,
          PrevWMQty = @var5,
          PrevGoogleQty = @var6,
          PrevOurCost = @var7,
          OurCost = @var8
        WHERE ItemID = @var9
END
ELSE
BEGIN
      UPDATE Products
      SET Active = 1,
          Pounds = @var1,
          Ounces = @var2,
          Qty = @var3,
          PrevQty = @var4,
          PrevWMQty = @var5,
          PrevGoogleQty = @var6,
          OurCost = @var8
        WHERE ItemID = @var9

END