I have a Stored Proc in MySQL that I want to use, in the WHERE clause of the SELECT Statement it will use a choice of 3 parameters
Example
SELECT * FROM MyTable
WHERE (Postcode = @postcode
OR StreetName = @streetname
OR JobNumber = @jobnumber)
I understand that if there was only 1 paramater in the WHERE clause it would be CALL MyStoredProc('thevariable') to Execute the StoredProc, but with it having 3 in a WHERE OR clause how do I Execute it?
You can add multiple parameters to a stored procedure.
DELIMITER $$
CREATE PROCEDURE test_procedure(post_code VARCHAR(8), street_name VARCHAR(64), job_number INT)
BEGIN
SELECT * FROM MyTable
WHERE Postcode = post_code
OR StreetName = street_name
OR JobNUmber = job_number;
END
DELIMITER ;
To then run it, you call it like any other, but pass it all the required parameters
CALL test_procedure('ABC123', 'Charles St', 5134);
You can read more about them here on the MySQL documentation website.
WHERE clause where some of the parameters may be null? You can use the same method I discussed in an answer here, let me know if you have trouble translating it to your specific example.WHERE ((Postcode = post_code OR job_number IS NULL OR street_name IS NULL) OR (StreetName = street_name OR post_code IS NULL OR job_number IS NULL) OR (JobNumber = job_number OR post_code IS NULL OR street_name IS NULL));