CODEWITHSUNDEEP
mysql
Jez
Jez

Reputation: 260

MySQL Stored Procedure using parameters and query

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?

Upvotes: 0

Views: 68

Answers (1)

Alex.Ritna
Alex.Ritna

Reputation: 1984

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.

Upvotes: 1

Related Questions