fetching data from a Stored Procedure

Important notice

Stored Procedure can perform destructive actions when called : deleting tables, inserting data, etc…

In Alpana, the Stored Procedure will be called every time data is fetched : every time anyone loads a dashboard or refreshes a filter pointing to such a Data Source, the Stored Procedure will be executed.

!Before executing a Stored Procedure, please make sure that it doesn’t cause unwanted side effects.
If necessary, instead of calling existing Stored Procedures, you can :

• create Table-Valued Functions or Stored Procedures that only SELECT data and don’t make changes to the data.
• create dedicated Stored Procedures for your dashboarding and reporting tools, or Alpana specifically, and make sure that they are safe
  Ask your database administrator for advice.
  

Stored Procedure without parameters

To get data from a Stored Procedure, browse to the desired Stored Procedure in the schema and drag it to the top main area :

If the Stored procedure takes no input parameters, then that’s all.

If the Stored procedure does take input parameters, then please proceed below :

Stored Procedure with parameters

If the Stored procedure takes input parameters, a dialog appears to let you set the parameters. It lists the input parameters and their data type :

Stored Procedure with static parameters

To fill in static values for the Stored Procedure parameters, simply fill the text boxes and click “OK” :

With a static value, the Stored Procedure will always be called with the value you entered.

Stored Procedure with dynamic parameters

Instead, you can bind a Stored Procedure parameter to an Alpana Dashboard Parameter (see the dedicated Course Chapter on Parameters).

This will allow you for example to change the Stored Procedure call depending on Master Widget clicks on the dashboard.

To do so, in the “Stored Procedure Parameters Editor” dialog, check “Use Parameter” and select an existing Alpana Dashboard Parameter :

Parameter configuration

If you are using multiple value parameter such as time range, maybe you would like to get only a specific item (i.e. start or end date) in this case, you will have to check the use 0-based value and select the index of the parameter wanted.

The Join seperator allows to separate string in multiple element (just like a csv) and select the parameter needed with the index.

You can also create a Parameter directly form this dialog by clicking the parameter list button :

*Note :
Be careful that some Stored Procedures require non empty parameters. In order to complete this step, you may have to set a default value to the Alpana Parameter :

Editing existing Stored Procedure parameter binding

Once the Stored Procedure parameters are bound, you can change them at any time by clicking the “Edit Parameters” cog icon on the corresponding table :

Stored procedure limitations

SQL Stored Procedures can do a lot of things, and some of them don’t even return data.

Alpana can only deal with certain types of stored procedures :

Single data set

Alpana supports stored procedures that return a single data set.

Stored procedures that return several data sets are not supported. For example :

SELECT 123;  -- first data set
SELECT 465;  -- second data set

… is not supported.

No output parameters

Stored procedures that use output parameters are not supported. For example :

CREATE PROCEDURE output_param 
@outparam INT OUTPUT
AS 
BEGIN 
SELECT @outparam = 123; 
END

… is not supported.

Code192 2020 All rights reserved