HOW TO CALL STORED PROCEDURE IN CODEIGNITER

1. Create a stored procedure with no parameters.
In many cases, storing MySQL procedures to call on later is ideal if you do it more than once. Before you can call the procedure though you’ll need to create it, define the steps of the procedure. In this example, we’ll create a procedure that has no parameters to pass. The name of this MySQL procedure is Select_foo. We are pulling all the rows and all the fields in those rows from table creatively named table_foo.

CREATE PROCEDURE Select_foo ()

BEGIN

SELECT * FROM table_foo;

END

 

Now that the Select_foo procedure has been created and defined, we can call on this procedure at anytime in CodeIgniter. In this example, we create a variable, $data, by pointing to the procedure above after loading and initializing the database class in CodeIgniter.  

2. Create a stored procedure with a single parameter.
What if you want to depend on some parameter to run a procedure? For example, let’s say we want to remove all records in the table table_foo that have a value of 13 for some column called id, simply because we’re superstitious like that.
With a stored procedure that depends on parameters, we can use IN/OUT keyword for passing data into the procedure. Let’s create a stored procedure that will delete a record that has a specific id equal to a variable.

 

CREATE PROCEDURE delete_bar(IN p_id int(10))

BEGIN

DELETE FROM table_foo WHERE id=p_id ;

END

 

In parameterized stored procedure you can use IN/OUT keyword for passing the data to the procedure. After IN, we are stating that the parameter value we pass to the procedure delete_bar(), whatever sits between ( and ) will then be known as p_id, which we also state is an integer. The variable p_id doesn’t mean anything to us outside of the procedure delete_bar. But, now that we’ve created it, all we need to do is create a variable to call delete_bar() and have the procedure ask for a parameter, which can be done by inserting a ? between the parentheses that follow the procedure name.
So let’s create a set of variables, one that holds our number 13 and one that calls the procedure to delete rows with a specific id. Just so you know, you can pass a parameter to the procedure from either the model or the controller.

$id = '13';

$stored_procedure = "CALL delete_bar(?) ";

 

But this won’t work just by itself. As it stands, the value for $id is not passing into the stored procedure, $stored_procedure. We can do this by using CodeIgniter’s database query function to bring both the called stored procedure and the id in one command and do the work for us.

 

$result = $this->db->query($stored_pocedure,array('id'=>$id));

 

With this our task is done. The id value passes into the called stored procedure delete_bar and all the rows that contain a value of 13 are deleted from the table. Now you can simply change the value of $id and define $result again to delete any row with a specific value for the column ‘id’.

 

3. Create a stored procedure with multiple parameters.
So what if you need to call a stored procedure that needs more than one value passed, such as when you add a new row to the table? However many parameters you intend to pass need to be defined in the call to the stored procedure itself. So if, for example, you want to pass a total of three variables into a called stored procedure, then you have to apply a “?” for each parameter needed, separated by a comma, within the parentheses following the name of the stored procedure.
First we create a procedure with multiple parameters, and call it add_foobar. Here it’s an INSERT command for MySQL.