Blog Post

Introduction to Stored Procedure:

A stored procedure is a precompiled collection of one or more SQL statements stored on the database server. They are used to encapsulate repetitive tasks, complex business logic, or a series of operations that need to be performed on the database. Stored procedures help improve the performance, maintainability, and security of database operations.

Key Benefits of Stored Procedures:

1. Performance: Since stored procedures are precompiled, they execute faster than individual SQL statements. The database server can optimize and cache the execution plan.
2. Maintainability: Encapsulating logic in stored procedures makes it easier to update and maintain without affecting application code.
3. Security: Stored procedures can help protect against SQL injection by parameterizing inputs. They also allow for fine-grained access control.

Types of Parameters in Stored Procedures:

Stored procedures can accept parameters to make them dynamic and reusable. These parameters can be classified into three types:

1. IN Parameters: These are input parameters that allow the caller to pass values into the stored procedure.
2. OUT Parameters: These are output parameters that allow the stored procedure to pass values back to the caller.
3. INOUT Parameters: These parameters can both receive input and return output.

STEP 1: Create a table called EmployeeDetail in the database and create a stored procedure.

STEP 2: Create a stored procedure in the database.

CREATE DEFINER=`<username>`@`<hostname>` PROCEDURE `UpdateEmployeeDetails`(
    IN Name1 VARCHAR(50),
    IN Age1 INT,
    IN Department1 VARCHAR(50)
)
BEGIN    UPDATE <table_name>
    SET Age = Age1,
        Department = Department1
    WHERE Name = Name1;
END

STEP 3: Log in to the Boomi Platform.

STEP 4: Click on the (+) sign to create a new component Process.

STEP 5: Select the Start shape as no data and click OK.

STEP 6: Now, we will provide the XML data through message shape. Click on the (+) beside the Start shape and choose Message shape.

STEP 7: Put the file in message shape and click OK.

STEP 8: Now take a Map shape.

STEP 9: Create one profile according to the data you are passing, choose the profile in the source of the map shape and keep the destination as empty as of now.

STEP 10: Click on Save & Close.

STEP 11: Take Database Connector.

STEP 12: Create a Database connection by clicking on +.

STEP 13: Provide the details like Database URL (you will get from MYSQL workbench), Username, Password, Host, Port, and Database Name (Schema Name). Then click on Save & Close.

STEP 14: Choose the action as Send.

STEP 15: Create one operation by clicking on +.

STEP 16: Create a database profile in the operation by clicking +.

STEP 17: Select the Statement and select the Stored procedure write as Type.

STEP 18: Click on the Import.

STEP 19: Choose the Atom in Browse In where the database is installed and select the connection. (Same connection created in Step 11) Click on Next.

STEP 20: Select the stored procedure which you have created in the database.

STEP 21: It will create a profile and click on Save & Close.

STEP 22: Take Stop Shape.

STEP 23: Click on Test. Select the Atom and click OK.

STEP 24: Check the database by running the query.

Query: SELECT * FROM <Schema_name>.<Table_name>;