How to execute MySQL Stored Procedure from Laravel 8.0^ with ANY parameters

Question

I have recently gone over SQL Stored Procedures using Laravel. I had a lot of issues and troubles trying to call my procedure with both input and output data. I use Laravel 8.0^.

Now, I went over this old question that helped a little but is now obsolete, and I would like to get the best possible way to call a procedure with input and output parameters

Answer 1

Laravel itself does not support any method Procedures call (like it does with DB::select() for SELECT queries), so you will have to write a Raw SQL call.

Now, there are 4 cases for procedures, and they depend on whether you are inserting or getting data from the procedure itself, or not getting anything at all. I will go over all of them:

parameterless procedure call

This type of call is a simple procedure executed from Laravel that needs no input data nor returns any. It can be done in such way:

$query = "CALL your_stored_procedure(); COMMIT;";

DB::statement($query);

procedure call with ONLY input parameters

This type of call is a simple procedure that is similar to what you have seen above, but we pass data in it. It can be done in 2 ways, depending on your needings:

//passing static data in the procedure
//your_data can be a String, int float, and so on depending on the type in the procedure
$query = "CALL your_stored_procedure(your_data); COMMIT;";

DB::statement($query);

//passing dynamic data in the procedure
$query = "CALL your_stored_procedure(:your_data); COMMIT;";

$bind = [
    'your_data' => $your_data
]

DB::statement($query, $bind);

procedure call with ONLY output parameters

This type of call is a simple procedure that is similar to what you have seen above, but we get data from it. It can be done this very simple way:

//getting data from the procedure
$query = "CALL your_stored_procedure(@your_output_data); COMMIT;";

//calling the procedure
DB::statement($query);

//getting the data from the variable in the procedure
$return_query = "SELECT @your_output_data AS your_output_data";

$result = DB::select($return_query);
DB::statement($return_query);

//doing what you want with the data you get
var_dump($result);

procedure call with BOTH input and output parameters

Like the above procedures, this is a mixture of them all and is aimed at showing how to call a procedure with data in it and getting data back from it:

//passing dynamic data in the procedure
$query = "CALL your_stored_procedure(:your_input_data, @your_output_data); COMMIT;";

$bind = [
    'your_input_data' => $your_input_data
]

DB::statement($query, $bind);

//getting the data from the variable in the procedure
$return_query = "SELECT @your_output_data AS your_output_data";

$result = DB::select($return_query);
DB::statement($return_query);

//doing what you want with the data you get
var_dump($result);

Why am I using COMMIT? COMMIT is used by the DBMS to make a permanent edit on the updated table where the procedure has been called. Almost every DBMS nowadays has a thing called AUTOCOMMIT that does this for us. However, this will not let us call a possible ROLLBACK in case we'd need to restore or cancel the changes made, and that's why I used COMMIT at the end of every procedure call

you are free to comment asking anything you need

N.B.

Mind the answer is aimed at showing HOW to make a procedure call on Laravel, not on how to handle the response from it (if there was any)