How to call a Stored Procedure and store a return value in a variable

In Stadium, it is possible to call stored procedures which return output values, and in turn display these output values in controls.

Scenario: Calculate Currency amount based on Exchange Rate

User enters Amount and Exchange Rate, and then clicks on the Calculate button. A calculated amount is displayed in a Label.

Steps :

Create a DetailsView with 2 Fields – Amount and Exchange Rate. Add a Calculate button and a Label called CalculatedValueLabel. DetailsView will be used to pass input values and Label will be used to display output values.

To the button Click event, add a variable (I named it calculateVariable).

Add ‘ExecuteSQLCommand’ event to the button.

Select a SQL connection in the Connection property. In the CommandDetails property, choose the Stored Procedure you want to connect to. Click on Refresh Parameters button. Parameters that are in the Stored Procedure are added automatically in Stadium.

In this example, there are 2 Input Parameters (value and exchangeValue) and 1 Output Parameter (calculatedValue).

Input parameter settings :

Assign the DetailsView field value to the Input Parameters (value and exchangeValue) using ‘Control’ Parameter Source.

Output parameter settings:

Set Parameter source for Output Parameter (calculatedValue) to ‘Output to Variable’. Select the output variable from OutputToVariable dropdown list. Type value can be selected from Boolean, DateTime, Int and String depending on the type of data you want to display.

Add SetControlProperties event after the ExecuteSQLCommand event. SetControlProperties event can be used to assign the variable value to other controls. In this example, we will be assigning it to a Label control.

Check on the Label. Select the variable that is used to store the output values (calculateVariable) as Text value.

Other Controls that can display values from Variables are :

• DetailsView
• DataGrid
• TextBox

In a more advanced usage, it can be used as an Input to other processes such as CallLinxProcess or passed to events within the same Click event such as DisplayMessageBox or Decision.

In summary, users can call a Stored Procedure, pass values to the Input Parameters and display results received from the Output parameter.

1. A Stadium control passes input values to Stored Procedure
2. Stored Procedure does the Calculation, then passes the result to a Stadium control
3. Stadium Control saves the results to a variable to be used when needed.