Stored Procedures in mysql

Create and Execute mysql Stored Procedures

Syntax to create  stored procedures

mysql  procedure 

Consider database Bus Transportation System

BUS (BUS_NO INT , CAPACITY INT , DEPOT_NAME VARCHAR(20),ROUTE_NO INT  FOREIGN KEY)

ROUTE (ROUTE_NO INT, SOURCE CHAR(20), DESTINATION CHAR(20), NO_OF_STATIONS INT)

Example : Create a stored procedure to display number of buses for given source 

mysql code to create above procedure

Let us execute above created procedure
Step 1. Change the delimiter other than ;. $$ will be our new delimiter

Step 2. Type a procedure code on MySQL terminal.

                                           
Step 3. After compilation of procedure we need to invoke procedure.

                                                       






We can see the businfo procedure accepted kothrud value for source and has stored count of buses form kothrud source into user defined variable buscount that was passed to businfo procedure.