SQL Stored Procedure example

On my SQL server, I have added a Store Procedure to my Stage database. The Stored Procedure is called dbo.numbers:

The simple sample script for the Stored Procedure is this:

You can copy/paste the script from here, if you like:

USE [Stage]
GO
/****** Object: StoredProcedure [dbo].[numbers] Script Date: 19-05-2022 12:04:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[numbers] 
-- Add the parameters for the stored procedure here
@count int = 0
AS
BEGIN
DECLARE
@init int = 1
WHILE @init <= @count
BEGIN
SELECT 2
SET @Init = @Init + 1
END
END

What the script does:

1. It declares a parameter, @count, with an initial value of 0. This @count parameter is the one receiving input from another external data source - e.g., a CSV file. In this case, the external input should be an integer value.
2. If the input value is 3, the WHILE loop will run three times (until @init <= 3).
3. For each loop, the Return value will be incremented by 2 (due to the SELECT 2 statement). So, for an input value of 3, the Return value will become 6.

Data Discovery - SQL Stored Procedure data source

In Data Discovery, you can select the SQL Stored Procedure data source type:

Fill in the provider's properties:

• Name: Any name you would lik for this data source.
• Server: Type in the name of the server holding the SQL server with the Stored Procedure.
• Database: In case of Windows Authentication, you will be able to look up the correct database.
• Stored procedure: Select the proper Stored procedure from this server.
• Parameter sample (optional): You can enter a parameter samle, e.g., '3', to try to autodetect the Metadata.
• Metadata: Or, alternatively, you can switch to 'Manual' mode for Metadata. This will give you an option to type in your own desired name for the Return value. In my example below, I have simply typed in 'ReturnValue' and hit the '+' button.

Data Discovery - input data

The Stored procedure will need some input data. In my example, it will need some integer values for the @count parameter.

In this example, my input data is stored in a CSV file - but it could be any other type of data source. The CSV file is also added as a data source to Data Discovery:

Data Discovery - cube

These two data sources, the Stored Procedure data source and the CSV data source, are now combined into a simple Data Discovery cube:

Stored Procedure data in the TARGIT client

In the TARGIT client, we can now build something like this, with no input value selected:

With one input value selected:

With two input values selected: