xxxxxxxxxx
# Drop the procedure if it is already created
DROP procedure IF EXISTS procedure_name;
# Procedure / Function definition that stores output in a variable
DELIMITER $$
CREATE PROCEDURE procedure_name (IN param1 INT, IN param2 INT, , OUT result INT)
BEGIN
SELECT * FROM some_table WHERE col1_val = param1 AND col2_val = param2;
END $$
DELIMITER ;
# Calling the procedure / function and store the query result in "@result"
CALL procedure_name(1,2, @result);
# See the result
SELECT @result;
xxxxxxxxxx
DELIMITER $$
DELIMITER $$
CREATE PROCEDURE SpGetAllStudents()
BEGIN
select * from students;
END $$
DELIMITER ;
#CALL SpGetAllStudents(); // calling the sp
#DROP PROCEDURE SpGetAllStudents; // deleting the sp
// call using php
$conn = mysqli_connect("hostname", "user", "password", "db", "port");
//run the store proc
$result = mysqli_query($conn, "CALL SpGetAllStudents");
//loop the result set
$rows = mysqli_fetch_assoc($result);
print_r($rows);