Other advantages of the use of stored procedures

Using a stored procedure increases productivity compared to a simple sequence of SQL instructions. This is because the procedure code is compiled once on the data storage server when it’s first running and after it is saved in a compiled form. In addition, the use of stored procedures considerably increases security. Since when calling a procedure over the network, you can see only a call to execute the procedure. The names of table and database objects are not available, and, accordingly, it is much more difficult to perform SQL injections.

The use of a stored procedure

Now we’re going to tell you how to use a stored procedure in reports. When creating reports and dashboards, you can type the text of a stored procedure call in the Data Source editor while editing or creating it. In this case, the report generator engine will execute a query with a stored procedure when rendering a report that will be processed on the server with data storage. As a result, an answer with data will be formed that will be got back to the report generator. Then, these data will be processed by the report generator and displayed in the viewer as a report or dashboard.

Parameters of a stored procedure

When calling a stored procedure, you can pass parameters. Using parameters for stored procedures depends on initializing the call text as Query or Stored Procedure. In cases of the call text initialization as Query, parameters are specified separated by commas, with a prefix in the form of a special sign @ before the parameter name. For example, exec StoredProcedureName @param1, @param2, @param3.

In cases of the call text initialization as a Stored Procedure, you should create parameters in a data source not specifying them in the text. The main thing here is to consider the sequence of parameters placement in the list. They will be applied sequentially, observing the order in the top-down direction from the list of parameters.

Also, you can get a list of necessary parameters of a procedure automatically. To do this, you should specify the name of the procedure in the call text, set the Type parameter to Stored Procedure, and select the Retrieve Columns and Parameters command from the Retrieve Columns menu.Note

In particular cases, a data source can support only unnamed parameters, for example, OleDB. In the case of the call text initialization as Query, it will be important to consider the sequence of parameters in the list. So, unnamed parameters in a query are denoted by a special symbol ? without naming them.

Besides, we have recently published a detailed article about parameters in a query. All described in it is applied to stored procedures, too. Including the fact that variables can be used as parameters of stored procedures with a value query from a user or by default.

We have explored the situation of using stored procedures when creating reports and dashboards. It’s up to you to decide whether to use queries or stored procedures, since each situation requires additional consideration to make a decision. Stimulsoft only provides various features and tools, making the process of creating reports and dashboards more flexible.

In addition, we have prepared a detailed video instruction about using a stored procedure when rendering a report.