Syntax for creating stored procedure in MySQL is almost same as in SQL Server. But there is a small problem which we need to keep in mind.
Usually stored procedure are going to be a group of queries. Each query will be separated by a delimiter. The default delimiter in MySQL is ";". So, this cause a problem in writing stored procedure because the MySQL will send a statement to execute to the server when it sees delimiter.
In our case we want to send the whole store procedure to be sent to the server to execute. So, the regular delimiter will cause issue. Because of this, we need to change the delimiter from semicolon to some other character. Failure to do this change will cause issues.
So we are creating store procedure using the below syntax
CREATE PROCEDURE `test`.`GetCustomers` ()
SELECT * FROM Customers;
In the above code, first line, changes the default delimiter to $$, and the last statement change the delimiter back to ";". After the Statement "END" we put the delimiter to tell MySQL that the stored procedure end here.
Store procudures will accept parameter to be passed when we call as below.
CREATE PROCEDURE `test`.`GetCustomerById` (customerId INT)
SELECT * FROM Customers WHERE Id = customerId;
We can execute the stored procedure by using "CALL" command as below.
Usually you can call stored procedure from PHP as below
$connection = mysql_connect(‘localhost’, ‘username, ‘password');
$result = mysql_query( ‘CALL StoredProcedureName()’ );
But, If your stored procedure will return set of rows them the above statement will not give us the resule back unless we open the connection using below statement. We need to pass additional parameters (false, 65536) to the connect statement
$connection = mysql_connect(‘localhost’, ‘username, ‘password',false,65536);
$result = mysql_query( ‘CALL GetCustomers()’ );
while($row = mysql_fetch_assoc($result))