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