Problem with piece of code

[ChrisFarrugia]

I have this stored procedure on which I would like to apply if statements at the where clause. But it seems that sql is finding errors associated with the if statements. Can anyone please guide me on what is wrong. Maybe it is not possible to use if statemnts in where clauses? @category int,@brand int ---> parameters AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; select equipment_id,equipment_modelNumber,equipment_description,category,brand,link,equipment_small_image,equipment_largr_image FROM Gen_Equipment INNER JOIN Gen_Equipment_Categories INNER JOIN Gen_Equipment_Brand INNER JOIN Gen_Equipment_Link ON (Gen_Equipment.equipment_category=Gen_Equipment_Categories.category_id) ON (Gen_Equipment.equipment_brand=Gen_Equipment_Brand.brand) ON (Gen_Equipment.equipment_link=Gen_Equipment_Link.link_id) WHERE if (NOT(@category=0) AND @brand=0) THEN BEGIN Gen_Equipment.equipment_category=@category order by Gen_Equipment.equipment_category END ELSE IF (@category=0 AND NOT(@brand=0)) THEN BEGIN equipment_brand=@brand END END Thank you very much for your help. Chris

[Colin Angus Mackay]

ChrisFarrugia wrote:

I have this stored procedure on which I would like to apply if statements at the where clause. But it seems that sql is finding errors associated with the if statements. Can anyone please guide me on what is wrong. Maybe it is not possible to use if statemnts in where clauses?

You can't use IF statements in WHERE clauses. IF statements stand alone and do not form part of another statement (in this case the SELECT statement) Your best bet is to put the IF statement around the SELECT

IF ((@category<>0) AND @brand=0)
BEGIN
SELECT equipment_id, equipment_modelNumber, equipment_description, category, brand,
link, equipment_small_image, equipment_largr_image
FROM Gen_Equipment
INNER JOIN Gen_Equipment_Categories
INNER JOIN Gen_Equipment_Brand
INNER JOIN Gen_Equipment_Link
ON (Gen_Equipment.equipment_category=Gen_Equipment_Categories.category_id)
ON (Gen_Equipment.equipment_brand=Gen_Equipment_Brand.brand)
ON (Gen_Equipment.equipment_link=Gen_Equipment_Link.link_id)
WHERE Gen_Equipment.equipment_category=@category
ORDER BY Gen_Equipment.equipment_category
END
ELSE
BEGIN
SELECT equipment_id, equipment_modelNumber, equipment_description, category, brand,
link, equipment_small_image, equipment_largr_image
FROM Gen_Equipment
INNER JOIN Gen_Equipment_Categories
INNER JOIN Gen_Equipment_Brand
INNER JOIN Gen_Equipment_Link
ON (Gen_Equipment.equipment_category=Gen_Equipment_Categories.category_id)
ON (Gen_Equipment.equipment_brand=Gen_Equipment_Brand.brand)
ON (Gen_Equipment.equipment_link=Gen_Equipment_Link.link_id)
WHERE equipment_brand=@brand
END

---

-- Always write code as if the maintenance programmer were an axe murderer who knows where you live. Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ... * Reading: SQL Bits My website