A stored procedure in MySQL is created using below syntax.

Stored procedure syntax
DELIMITER $$
CREATE PROCEDURE [procedure name]()
BEGIN
      [Statements of procedure]
END $$
DELIMITER
Explanation
A procedure is created with a CREATE PROCEDURE statement followed by the name of procedure.
Name of the procedure is followed by parenthesis which contain optional parameters accepted by the procedure. Stored procedure parameters will be discussed in the next section.
A procedure is enclosed between BEGIN and END statements which contain the body of procedure or the set of SQL queries of the procedure.
What is DELIMITER
Default delimiter of MySQL is a semi-colon which means that when MySQL encounters a semi-colon, it treats it as the end of a statement.
Now, when multiple SQL queries are written inside a procedure, they will also end with a semi-colon.
So, after the first SQL query, MySQL will consider it as the end of the procedure.
Hence, before starting the procedure, we change the default delimiter using DELIMITER statement followed by the delimiter we want.
After the procedure ends by using END statement, we place the same delimiter just to indicate MySQL about the end of procedure.
Finally, we reset the delimiter to default, that is, a semi-colon. Delimiter for a procedure can by anything such as **, && etc. $$ is just an example.
Executing Stored Procedure
A stored procedure can be invoked using CALL statement followed by the name of the stored procedure and parenthesis.
Parenthesis should contain the parameters expected by the stored procedure. If the stored procedure does not expect any parameters, then the parenthesis should be empty.
Examples of calling a stored procedure are given below.

— invoke procedure named getdata without any parameters
CALL getdata();

— invoke procedure fetchprice with item code as parameter
CALL fetchprice(123)

— invoke the same procedure with a variable name
SET itemcode=123;
CALL fetchprice(@itemcode);

Dropping a procedure
A stored procedure can be deleted using DROP PROCEDURE statement followed by the name of the procedure as shown below.

DROP PROCEDURE getdata;

If the procedure does not exist, then you will get an error stating the procedure does not exist.

0

Share your thoughts !!

Close Menu

Never Miss an article !

Get the new post delivered straight into your inbox, enter your email and hit the button

You have successfully subscribed to the newsletter

There was an error while trying to send your request. Please try again.

codippa will use the information you provide on this form to be in touch with you and to provide updates and marketing.