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.

  • 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 :

DataSource add stored procedure

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 parameters

Stored Procedure with static parameters

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

stored procedure parameters static

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 :

stored procedure parameters dynamic

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

use parameter
param default value

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 edit

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.

Last modified: Mar 13, 2019

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment