In this article we will discuss the use of stored procedures to retrieve data for a report. Using the MS SQL example, I'll show you how to create and use functions in a report.
As you know, Transact SQL allows us to create stored procedures and functions. Stored procedures are used to automate any action. The functions are intended to expand the capabilities of queries. They can be executed as part of a query and always return a value. Stored procedures are called by execute instructions and cannot return any values.
To retrieve data in a report, you can use both a stored procedure and a function.
But the topic of this article is the use of stored procedures.
For example, here is the simplest stored procedure, which will return the list of employees by the unit name:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Description: Returns employees by department -- ============================================= ALTER PROCEDURE dbo.DepartmentEmployee @DepName varchar(20) AS BEGIN SELECT em.*, dep.NAME FROM dbo.DEPARTMENT dep JOIN dbo.EMPLOYEE em ON em.DEPT_ID = dep.DEPT_ID WHERE dep.NAME = @DepName END GO
And now we use this stored procedure in the report. Create a connection to the MS SQL database:
At the stage of selecting tables, press the button:
The Create Query Wizard starts. In the first step, you need to specify the name of the new table, we proceed further.
Enter the text of the SQL query. We will send the report parameter as the value of the query parameter.
In the next step, create the @Dep parameter.
In the Expression property, specify the report parameter.
The value of the report parameter can be specified in the Expression property:
You can also set the value of the parameter in the dialog form before the report is displayed.
We transfer the fields from the data source in the data window to the data band. Run the report:
As you can see, all employee records for the "Operations" department are displayed.