When creating reports or dashboards, a Stored Procedure is often used to get data. In this article, we’re going to tell you about how to call stored procedures for popular data sources, the intricacies of creating a data source with stored procedures, and how to pass parameters to them.
What is a stored procedure?
A stored procedure is a SQL instruction that allows you to execute a set of different queries sequentially. It’s convenient to use when creating reports and dashboards when the same SQL query is duplicated all the time in various data sources. That’s why, for example, when some queries need to be edited, they should be changed in all reports. In this case, it will be logical to create a stored procedure in the storage and just call only this procedure when creating a data source in reports. Flexible use is the first advantage of stored procedures.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.Stored procedure initialization
When typing the text of a stored procedure call in a data source, the text of the call can be initialized as Query or Stored Procedure. You can set initialization of the call text using the Type parameter, having selected an appropriate value as Query or Stored Procedure.For example, if some call text is a query with a command of executing a stored procedure, the entire call text should be initialized as Query. Accordingly, a special stored procedure execution command should be specified before a stored procedure name. You can learn extensively about the commands of a stored procedure execution in the specification of your data storage. We just note that depending on a type of a data storage common commands of a stored procedure execution are
exec, execute and call.In cases when creating a data source, a stored procedure is executed only. The call text can be initialized as Stored Procedure. To do this, in the call text, you should specify only the name of a stored procedure that needs to be executed.
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.
If you have any questions, please contact us.