How to use stored procedures in FastReport .NET

2018-08-14

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.

September 19, 2024

How to troubleshoot the most common issues when installing FastReport VCL

We have compiled a list of the most popular problems when installing FastReport VCL and have prepared detailed instructions on how to fix the problems that have arisen.
August 12, 2024

How to build and install the Postgres plugin in FastReport .NET

This article describes how to connect to the database using the FastReport .NET plugin for the report designer from Visual Studio via the NuGet server.
August 08, 2024

How to install FastReport .NET and its components on Windows

Step-by-step instructions for online and manual installation via the FastReport registration code.NET and its components in Windows.
Fast Reports
  • 800-985-8986 (English, US)
  • +4930568373928 (German)
  • +55 19 98147-8148 (Portuguese)
  • info@fast-report.com
  • 901 N Pitt Str #325 Alexandria VA 22314

© 1998-2024 Fast Reports Inc.