Create a Dataset Using Stored Procedure

This guide walks you through creating a dataset in AIV using a stored procedure. Stored procedures centralize business logic in the database so your datasets stay simple and consistent.

What You’ll Learn

What a stored procedure is and when to use it
How to select a stored procedure and configure the dataset
How to review, preview, save, and view your dataset

What is a Stored Procedure?

A stored procedure is a database object that contains one or more SQL statements stored and executed as a single unit. You can use it as the source for a dataset so that complex logic stays in the database.

Characteristic	Description
Format	SQL statements stored in the database
Execution	Called by name (e.g., `CALL GetAllProducts()`)
Use case	Centralized logic, security, maintenance, reuse

Why use stored procedures for datasets

Centralize logic – Business rules live in the database; all datasets use the same logic.
Improve security – SQL logic is hidden from end users.
Simplify maintenance – Update the procedure once; all datasets reflect the change.
Reuse result sets – Call the procedure by name instead of repeating complex SQL.

Prerequisites

Before you begin:

Have the database connection for the target database (e.g., SampleDB) configured in AIV
Know the procedure name, any parameters (and their types), and the output columns it returns
Ensure the stored procedure already exists in the database you will select

Need to create or edit a procedure?

Use MySQL Workbench (or another MySQL client)
See Learn MySQL: the basics of MySQL stored procedures for help

Video Tutorial

Watch how to create a stored procedure in MySQL Workbench:

Steps to Create a Dataset Using Stored Procedure

Complete Step 1 (Navigate to the Dataset Section), Step 2 (Create a New Dataset), and Step 3 (Define Dataset Details) from the Create Dataset guide.

Step 1: Select Database Connection

Select the database connection that hosts your stored procedure (for example, SampleDB). Use the search bar to filter if you have many connections.

For details on selecting or creating database connections, see Database and Connected Sources.

Step 2: Select the Stored Procedure

In the Dataset Editor, locate the Stored Procedures section in the left panel (Database Explorer).
Select the required stored procedure.
The Stored Procedure checkbox is automatically enabled.
The system automatically generates the CALL query in the query editor.

Example – Without Parameters:

CALL GetAllProducts();

Stored Procedure Without Parameter

Example – With Parameters

Provide the required values in the generated CALL statement:
```
CALL GetProductsByBrand('Levis');
```
Only stored procedures available in the selected database are listed.
If the procedure has parameters, provide the required values before proceeding.

Step 3: Configure Output Columns

Open the Output Columns tab.
Review the columns returned by the stored procedure.
Optionally rename columns, adjust data types, apply formats, or disable columns not needed.

For detailed options, see Output Columns Tab Terminology.

Step 4: Preview the Result

Click Preview (or Preview Result).
Verify the result grid: correct rows, required columns, and expected data types.
Adjust parameters or output columns if needed and preview again.

For detailed options, see Preview Tab Terminology.

Step 5: Save Dataset

Click Save.
In the Save Dataset dialog, optionally rename the dataset and select the destination folder.
Click Save Dataset to confirm.

Step 6: View Your Dataset

Go to the Dataset Grid view.
Search for your dataset by name.
Your dataset is now available for use in dashboards, reports, and visualizations.

Explore other ways to create datasets in AIV:

Data Source	Guide
Drag & drop	Create Dataset
Excel	Dataset using Excel
CSV	Dataset using CSV
JSON	Dataset using JSON
Parquet	Dataset using Parquet
NoSQL	Dataset using NoSQL
Flat files	Dataset using Flat files
Google BigQuery	Dataset using Google BigQuery
Google Sheet	Dataset using Google Sheet
External sources	Dataset using External sources
View	Dataset using View