Why can't I use an if statement inside a select statement? Does anyone know another method?

Select b.ItemKey, a.CatKey, a.ParentKey, a.CatName, g.ItemID, c.UserFld1, c.UserFld3, c.UserFld5, f.UOMID, e.SizeValue
From dbo.timWebCategory a Inner JOIN
    dbo.timWebCategoryItem b ON a.CatKey = b.CatKey Inner JOIN
    mas500_app.dbo.timItem c ON b.ItemKey = c.ItemKey Inner JOIN
    dbo.cpsIMItemDetails d ON c.ItemKey = d.ItemKey I开发者_如何学JAVAnner JOIN
    dbo.timItemSize e ON c.ItemKey = e.ItemKey Left JOIN
    dbo.timVolumeUOM f ON e.VolUOMKey = f.VolUOMKey Left JOIN
    mas500_app.dbo.vWebItem g ON c.ItemKey = g.ItemKey
Where a.Catkey = @Key
AND d.IntFlg = 1
If @color is not Null 
Begin
    AND c.UserFld1 = @color
End
Order By f.UOMID, e.SizeValue**

Returns this: Incorrect syntax near the keyword 'AND'.

Try that...

Select b.ItemKey, a.CatKey, a.ParentKey, a.CatName, g.ItemID, c.UserFld1, c.UserFld3, c.UserFld5, f.UOMID, e.SizeValue
From dbo.timWebCategory a Inner JOIN
    dbo.timWebCategoryItem b ON a.CatKey = b.CatKey Inner JOIN
    mas500_app.dbo.timItem c ON b.ItemKey = c.ItemKey Inner JOIN
    dbo.cpsIMItemDetails d ON c.ItemKey = d.ItemKey Inner JOIN
    dbo.timItemSize e ON c.ItemKey = e.ItemKey Left JOIN
    dbo.timVolumeUOM f ON e.VolUOMKey = f.VolUOMKey Left JOIN
    mas500_app.dbo.vWebItem g ON c.ItemKey = g.ItemKey
Where a.Catkey = @Key
AND d.IntFlg = 1
AND (@color = c.UserFld1 OR @color is null)
Order By f.UOMID, e.SizeValue**

Turn that "inside out". You can't conditionally insert the AND, you have to always say the AND, and you may then use if-else (or just an OR, like the other answer) to conditionally provide either the condition you want or just true/false.

You can use the if statement to control which statements are executed, but you can't use it to remove part of a query.

One reason that it's not built that way is that the execution plan for the query would be different depending on the input, so a new execution plan would have to be created each time and could not be resused.

There are different ways to handle this, like using the isnull function to compare the value to itself if the varaible is null:

AND c.UserFld1 = isnull(@color, c.UserFld1)

Or checking for null before comparing:

AND (@coor is null or c.UserFld1 = @color)

You can always put the if statement around the query, which would give you two versions of the query, each with a slightly better plan than checking the value when running the query. Sometime that's worth the duplication of the code:

If @color is not Null Begin

  Select b.ItemKey, a.CatKey, a.ParentKey, a.CatName, g.ItemID, c.UserFld1, c.UserFld3, c.UserFld5, f.UOMID, e.SizeValue
  From dbo.timWebCategory a Inner JOIN
    dbo.timWebCategoryItem b ON a.CatKey = b.CatKey Inner JOIN
    mas500_app.dbo.timItem c ON b.ItemKey = c.ItemKey Inner JOIN
    dbo.cpsIMItemDetails d ON c.ItemKey = d.ItemKey Inner JOIN
    dbo.timItemSize e ON c.ItemKey = e.ItemKey Left JOIN
    dbo.timVolumeUOM f ON e.VolUOMKey = f.VolUOMKey Left JOIN
    mas500_app.dbo.vWebItem g ON c.ItemKey = g.ItemKey
  Where a.Catkey = @Key
    AND d.IntFlg = 1
    AND c.UserFld1 = @color
  Order By f.UOMID, e.SizeValue

End Else Begin

  Select b.ItemKey, a.CatKey, a.ParentKey, a.CatName, g.ItemID, c.UserFld1, c.UserFld3, c.UserFld5, f.UOMID, e.SizeValue
  From dbo.timWebCategory a Inner JOIN
    dbo.timWebCategoryItem b ON a.CatKey = b.CatKey Inner JOIN
    mas500_app.dbo.timItem c ON b.ItemKey = c.ItemKey Inner JOIN
    dbo.cpsIMItemDetails d ON c.ItemKey = d.ItemKey Inner JOIN
    dbo.timItemSize e ON c.ItemKey = e.ItemKey Left JOIN
    dbo.timVolumeUOM f ON e.VolUOMKey = f.VolUOMKey Left JOIN
    mas500_app.dbo.vWebItem g ON c.ItemKey = g.ItemKey
  Where a.Catkey = @Key
    AND d.IntFlg = 1
  Order By f.UOMID, e.SizeValue

End