Skip to main content

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.
Note:

To know more in detail about how to create Store Procedure in MySQL Workbench. Click here

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 Docusaurus Slash Introduction > MasterData > Dataset.

    Docusaurus Slash Introduction

    Docusaurus Slash Introduction

  • To know more in details about Dataset Section Click here .
  1. User can create dataset using 2 options:-

I. From the Footer Menu

Docusaurus Slash Introduction

II. From the Context Menu

Docusaurus Slash Introduction

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

    Docusaurus Slash Introduction

Note

To know more about terminology of General tab Click here

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

  1. In Datasource option JDBC is selected by default.

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

    Docusaurus Slash Introduction

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

  4. For this example we used below given query:

Query

call new_procedure();

Docusaurus Slash Introduction

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

    Docusaurus Slash Introduction

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