Create Dataset using Stored Procedure

What is Stored Procedure?

• User can Store data at location & call it directly from the Query window. User can’t able to see the internal code or query while using this option.

• A stored procedure is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database, and run as a unit to solve a specific problem or perform a set of related tasks. Procedures let you combine the ease and flexibility of SQL with the procedural functionality of a structured programming language. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures, and all applications call the procedures only.

Prerequisites

• To create dataset by creating Stored Procedure in MySQL. User needs to work with MySQL Workbench.

Mysql-Stored Procedure: This video contains how to Create Simple Stored Procedure in Mysql Workbench.

1. Log in to AIV using your respective credentials.

2. Go to Hamburger Menu > MasterData > Dataset.

   

   

• To know more in details about Dataset Section Click here .

3. User can create dataset using 2 options:-

I. From the Footer Menu

II. From the Context Menu

4. Click on Create Dataset option & your screen will look as per below image.

   

5. Enter the details in Create Dataset window as per below:-

• Name: Demo Dataset (user can select name as per their requirement)
• Source: Select aiv from drop-down (same name as you set it when you are creating database connection)

6. In Datasource option JDBC is selected by default.

7. Select Stored Procedure Checkbox as shown in image below:

   

8. Now user needs to write query to call Stored Procedure from MySQL Workbench.

9. For this example we used below given query:

:::note[Info] Query

call new_procedure();

:::

10. Now Click on Preview button & user can see the output.

    

11. Click on Submit button & dataset will be created.