logo
small logo
  • Products
  • Buy
  • Support
  • Articles
  • Customer panel Support
    • en
    • ru
    • pt
    • es
    • de
    • pl
    • JP
    • ZH
  • Home
  • /
  • Articles
  • /
  • How to use stored procedures in FastReport.Net
  • How to use Online Designer in ASP .Net Core

    January 17, 2018

    One of the novelties by FastReport .Net 2018 was the adaptation of OnlineDesigner to the

    read more
  • How to use WebReport with ASP .Net Web Core application

    January 17, 2018

    Recently FastReport introduced a new library under the .Net - FastReport Core platform. This is

    read more
  • FastCube - High-Speed OLAP Cube Engine and Pivot Grid

    February 29, 2020

    It is difficult to imagine data analysis without OLAP (On-Line Analytical Processing) technology. Although there

    read more
  • Report generators family FastReport brief review

    March 23, 2020

    Without reporting it is impossible to carry on business in any sphere of life. Bureaucracy

    read more
  • How to use stored procedures with multiple sets of data as a result

    May 8, 2020

    Often, when creating reports, we have to deal with databases that are far from ideal.

    read more

How to use stored procedures in FastReport.Net

August 14, 2018

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.

about product download buy
avatar
Dmitriy Fedyashov
Head of QA
.NET FastReport SQL

Add comment
logo
  • 800-985-8986 (English, US)
  • +4930568373928 (German)
  • +55 19 98147-8148 (Portuguese)
  • info@fast-report.com
  • 901 N Pitt Str #325 Alexandria VA 22314
  • Buy
  • Download
  • Documentation
  • Testimonials
  • How to uninstall
  • Ticket system
  • FAQ
  • Tutorial Video
  • Forum
  • Articles
  • Our News
  • Press about us
  • Resellers
  • Our team
  • Contact us

© 1998-2021 by Fast Reports Inc.

  • Privacy Policy