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
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)