To Pass Parameters to a Stored Procedure

First, you create MySQL database salary table consisting of the employee number (primary key), salary, and bonus. Next, you create a stored procedure to give employees an additional bonus. You create and run a Mule app that has a database connector. The connector calls the stored procedure, passing the employee number and bonus amount as parameters. The connector updates the bonus in the employee record.

You can get the XML code for this app from the following URL. Modify the credentials and endpoint in the database connector global element for your MySQL instance.

https://docs.mulesoft.com/mule-user-guide/v/3.9/_attachments/database-stored-task.xml.zip

Create the company database if you haven’t already created it. On the mysql command line or in the MySQL Admin, create a salary table as follows:
```
CREATE TABLE salary (
emp_no INT NOT NULL,
salary_amount INT NOT NULL,
bonus INT NOT NULL,
PRIMARY KEY (emp_no)
);
```

On the mysql command line or in the MySQL Admin, execute these INSERT statements to populate the table.

INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1011,75000,1000);
INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1012,50000,1000);
INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1013,50000,1000);
INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1014,70000,1000);
INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1015,150000,1000);
INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1016,50000,1000);
INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1017,75000,1000);
INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1018,70000,1000);
INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1019,50000,1000);
INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1020,50000,1000);
INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1021,75000,1000);
INSERT INTO salary (emp_no,salary_amount,bonus) VALUES (1022,75000,1000);

Run the following MySQL commands on the mysql command line or in the MySQL Admin to create the stored procedure.

DELIMITER //
CREATE DEFINER=root@localhost
PROCEDURE add_bonus_by_emp_no(IN EMP_KEY integer, IN ADDL_BONUS integer)
BEGIN UPDATE salary
SET bonus = ADDL_BONUS + bonus
WHERE emp_no=EMP_KEY;
END //

In Studio, create a new project, and drag connectors onto the canvas as follows:
- Drag an HTTP connector onto the canvas and set the path to /getemps.
- In Connector Configuration, click Add . Accept the default options or change options to match your environment, and save.
  
  The HTTP error indicator extinguishes.
- Drag a Set Payload connector onto the process section of the canvas, set its value to the following expression, and save:
  
  #[message.inboundProperties.'http.query.params'.emp]
- Drag a Database connector to the canvas and configure it as follows:
  - Connect to the company database.
  - In Operation, select Stored Procedure.
  - In Parameterized Query, call the stored procedure:
    
    CALL add_bonus_by_emp_no(:empid,500);
  - In Parameters, click Add Parameter , and enter the following options:
    
    Parameter Name: empid
    
    Value: #[payload]
    
    Accept other defaults and save.
Run the project as a Mule app.
Assuming your host is localhost, activate the flow by entering the following URL in a browser or in a curl command.
```
curl http://localhost:8081/getemps?emp=1011
```
The employee ID is passed as an input parameter and ingested by the application.
Check that the bonus value in the salary table has increased from 1000 to 1500 for employee 1011.

To Pass Parameters to a Stored Procedure

See Also