How to use stored procedures in FastReport.Net

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.