Stored Procedures

Chapter 11 - Using SQL in Crystal Reports
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

Stored procedures are a series of SQL commands that are stored in the database under a name that can be referenced by a calling application. The SQL commands execute sequentially on the server whenever the stored procedure is called by name. So you can think of a stored procedure as a code module.

These code modules are typically created by a database administrator or programmer and stored in a table in the database itself. Stored procedures run on the database server in server memory after being invoked by a client application. The result set is then sent back to the calling client application, in our case, Crystal Reports. Typical uses of stored procedures in a database include:

• Joining large tables and sorting and grouping the data

• Performing a series of queries

• Combining the results of one query with another

• Generating statistics based on stored data

Programmers generally decide to create a stored procedure if they find themselves coding the same or similar SQL queries over and over again. Writing a parameterized query can simplify their jobs. The main advantages to using stored procedures from Crystal Reports are security, performance, reusability, and maintainability.

Security

Stored procedures are stored in a table in a database. Since permissions are granted on a table-by-table basis, security can be put in place to control who can run stored procedures. This centralizes the administration of security, moving it away from the plethora of applications that use the data and placing it closer to the data itself.

Performance

If Crystal Reports is processing large amounts of data but ultimately displaying only a subset of that data due to filtering and other formulas, it can generate a lot of network traffic because all of the data has to be present in Crystal before final processing. A stored procedure does this processing on the server, which sends only the required end result data back to the client, therefore reducing network traffic. In addition, once a stored procedure has been run, it becomes a compiled module and is cached in server memory so that future calls to it will be faster than the first. A client application takes advantage of the superior processing capabilities of a server without impacting its own processing resources. Also, the more individual SQL statements a database has to handle, the more database locks are used; stored procedures can process SQL statements in groups and reduce the number of database locks being used, which helps overall database performance for other applications as well.

Reusability

Once you’ve written a very complex SQL SELECT statement, you’ll find that you’ll copy and paste it, tweak it, and fine-tune it for years to come for a variety of applications. A good use of a stored procedure is to group together SQL statements that can be called in a block to return results.

The goal is to write a stored procedure once, test it exhaustively until it’s perfect, and then call it frequently instead of writing the SQL statements from scratch every time.

Maintainability

In any organization, it is typical to have multiple people who need to execute the same queries over time. By saving the queries as stored procedures, you increase the reliability of the results as well as having only one place to look for problems or to make a programming change. For example, if the stored procedure is being called from multiple web pages and something changes in the query, you can change it once rather than changing it in every web page, because the web page is simply invoking a routine that is stored on the server.

Stored Procedures As a Crystal Data Source

The result of executing a stored procedure in a database is the return of a result set of data. The result set is a virtual table held in the database’s memory. Since this is a virtual table, and it is represented in row and column format, it can be used as a data source in Crystal. If the database being connected to has stored procedures, they will be listed as a data source below the Tables option. Figure 11.18 shows stored procedures that are accessible in the miracleSqlServer data source that has been connected to using OLE DB.

Choosing a stored procedure from the list of Available Data Sources in the Database Expert identifies the result of the stored procedure to be the data set Crystal Reports will process. The result set is treated as a table object in Crystal Reports.

Figure 11.18. Stored procedures as a data source

The stored procedure is actually executed and run on the server when the finished Crystal Report is opened. Figure 11.19 shows that the stored procedure named EmpDeptJob was run and the result was that the fields DeptDesc, JobTitle, FPTime, Salary, FName, and LName were returned to Crystal for use in the report.

Figure 11.19. Stored procedure results

Tip 

If a database’s stored procedures do not automatically appear in the Database Expert, you can verify that Crystal Reports is set to display them by choosing File > Options, selecting the Database tab, and enabling the Stored Procedures check box.

Stored procedures are very powerful in that they offload a good deal of processing onto the server. However, there are some guidelines that limit how stored procedures can be used within Crystal data sources:

• The stored procedure cannot change data in the database, meaning it needs to use SELECT statements only.

• If a stored procedure is used, it is the only data source allowed.

• A report can use only one stored procedure as a data source at a time.

• Any joins on tables must be part of the stored procedure code.

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback