Re: Please Help Need Search Stored Proc

[John Gilmour]

Thanks. I believe I've come up with the correct logic for the WHERE clause, however I'm still not clear on the logic for the parameters. Here is what I have...I think I just need the logic of the IN parameters to allow for the parameter to be skipped if it's null. I tried setting them all = NULL but kept getting error messages. Any help would be appreciated, I'm really beating my head against the wall with this...

CREATE ROCEDURE `sp_test2`(
IN mydoors INT,
IN mymodel INT ,
IN mycolor INT ,
IN myyear YEAR ,
IN myretailprice DECIMAL(10,2) ,
IN mydealercost DECIMAL(10,2) ,
IN myinteriorcolor INT ,
IN mymanufacturer INT)
BEGIN


SELECT car.id
, model.manufacturer
, model.modelname
, car.year
, color.color_name
, interior_color.interior_color
, car.doors
, car.retailprice
, car.dealercost
, transmission_type.transmission_type

FROM car
INNER JOIN model ON (car.model_id=model.id) INNER JOIN color ON (car.color_id=color.id) INNER JOIN interior_color ON (interior_color.id=car.interior_color_id) INNER JOIN transmission_type ON (car.transmission_type_id=transmission_type.id)
WHERE
(mydoors IS NULL OR car.doors LIKE 'mydoors')
AND (mymodel IS NULL OR model.id LIKE 'mymodel')
AND (mycolor IS NULL OR color.id LIKE 'mycolor')
AND (myyear IS NULL OR car.year LIKE 'myyear')
AND (myretailprice IS NULL OR car.retailprice LIKE 'myretailprice')
AND (mydealercost IS NULL OR car.dealercost LIKE 'mydealercost')
AND (myinteriorcolor IS NULL OR interior_color.id LIKE 'myinteriorcolor')
AND (mymanufacturer IS NULL OR model.manufacturer_id LIKE 'mymanufacturer')
ORDER BY car.id;
END