Before you can execute a stored procedure or report on your data server using Telerik Platform you need to map it to an HTTP endpoint. You also need to map its parameters and, optionally, change their default values. Once you have done that, you are ready to execute the stored procedure.
This article focuses on how to do this using the portal. For information on using the RESTful API, see the rest of the articles in this sections.
Content at a glance:
- Give the Endpoint a Name
- Select a Stored Procedure to Map
- Map the Stored Procedure Parameters
- Set Permissions
- Execute a Stored Procedure
- A Telerik Platform account with a Telerik Platform Enterprise subscription
- A Telerik Platform with Business Logic and Data services enabled
- A working Data Link Server
- A configured and tested Data Connector to the SQL database whose stored procedures you want to access
Start by giving the stored procedure endpoint a name. The name becomes a part of the web service endpoint that you use to execute the stored procedure (from the portal or the RESTful API). It must be unique within your Telerik Platform app.
- Open your Telerik Platform app.
- From the left-hand navigation pane choose Business Logic > Explorer.
- Click the Create button in the middle pane and select Function from a stored procedure. Alternatively, click the + button next to Stored Procedures in the code explorer on the right.
- Enter a name in the modal dialog and click OK. You can change the name later.
Telerik Platform reaches to your database servers and reads each database's schema and catalogs to find stored procedures along with their parameters. After it is ready, you are presented with a screen to choose a stored procedure:
- From the Select Data Connector drop-down box, select the data connector that you want to use.
- From the Select source Stored Procedure select the name of the stored procedure that you want to map.
- Optionally, change the name of the endpoint in Function name.
Mapping parameters is currently not supported when connecting to Salesforce. Salesforce reports appear without parameters.
The parameters of a stored procedure (if any) are automatically read at the time of loading the stored procedure from the database schema. Telerik Platform automatically binds each of them to an HTTP endpoint parameter. You can:
- Rename the endpoint parameter by entering a new name in the respective text box
- Select the parameter type using the drop down box for the parameter
- Specify a default value by clicking the gear icon and entering the value
- Mark the parameter as required by clicking the gear icon and clicking the respective check-box
If you remove a parameter from the stored procedure statement in the SQL database, the respective parameter is removed from the HTTP endpoint parameter mapping as well. Similarly, adding parameters in the SQL database adds parameters to the procedure definition in your Telerik Platform app. In both cases you need to update the endpoint mapping for the changes to take effect by clicking the Save button.
To specify who can execute the mapped stored procedure, click the gear icon next to the stored procedure name in the code explorer and select Permissions.
You can read more about permissions in Security in Stored Procedures.
After you map a stored procedure, you can immediately execute it in the portal. This option can be useful for quick testing or if you only execute the function manually. In those cases you don't need to write any code to call the endpoint.
Take these steps to execute a stored procedure from the portal:
- Select the stored procedure name in the code explorer's code tree.
- Click the Execute button in the toolbar.
Define the request in the dialog box that opens.
Click Execute in the dialog box.
Normally, you would execute a stored procedure programmatically using its unique HTTP endpoint. See Executing Stored Procedures for information on how to do that.
- Introduction to Stored Procedures
- Mapping Stored Procedures
- Executing Stored Procedures
- Reading Stored Procedure Mappings
- Getting the Stored Procedure Endpoints Count
- Updating Stored Procedure Mappings
- Deleting Stored Procedure Mappings
- Security in Stored Procedures
- Limitations of Stored Procedures
- Stored Procedures Fields and Values Reference