Using SQL Stored Procedures

Most SQL database systems include the capability to use stored procedures. A stored procedure is a SQL query that has been evaluated, or compiled, by the database server in advance and is stored on the server along with regular database tables. Because the stored procedure is compiled in advance, it often performs faster than a SQL query submitted on the fly. Stored procedures can be created by the database designer or administrator for specific queries that will be run on a frequent basis.

To enhance flexibility, stored procedures can contain one or more stored procedure parameters that prompt the user to enter a value. The stored procedure then uses the value to run the query. For example, if you have a stored procedure that returns several fields from linked tables, based on Country and Order Date parameters, you ll be prompted to enter a particular country and order date when the stored procedure runs. The procedure will then return a result set containing only records matching the two-parameter values you supplied.

Choosing Stored Procedures

Crystal Reports treats stored procedures almost identically to regular database tables. Stored procedures appear in the Database Expert and are used in a report just like regular database tables. The only difference is that a stored procedure may have parameters associated with it. However, you do have a choice of whether or not stored procedures will appear in the Database Expert in the first place. The quickest way to ensure that they show up is to right-click in the Available Data Sources portion of the Database Expert and choose Options from the pop-up menu. Then, ensure that the Stored Procedure check box in the Database Options dialog box is checked (shown earlier in Figure 16-3). You may also make the change permanent for all new reports in the future by checking the same option on the Database tab after choosing File Options from the pull-down menus .

Now, when you open a SQL database, you ll see stored procedures appear along with regular database tables.

Tip  

Crystal Reports allows you to link other database tables to Stored Procedures. Just be aware of limited join and link types, as discussed earlier in the chapter under Using Multiple Database Types in the Same Report.

Working with Stored Procedure Parameters

After you choose a stored procedure to report on, you are prompted to supply any values for any stored procedure parameters in the Enter Parameter Values dialog box. This dialog box is identical to the one that prompts for report parameter fields, discussed in Chapter 14.

Type your desired parameter values and click OK. You can then simply continue your report design process normally. The stored procedure will supply a list of fields you can use in your report, just like a normal database table.

Stored procedure parameters behave almost identically to Crystal Reports parameter fields. The stored procedure parameters will appear in the Parameter Fields category of the Field Explorer. You can edit a stored procedure parameter by selecting it and clicking the Edit button, typing CTRL-E, or right-clicking the parameter and choosing Edit from the pop-up menu. As with SQL Command “based parameters (discussed earlier in the chapter), you don t have the choice of advanced parameter types, such as multi-value or range.

You can, however, click the Set Default Values button to supply a pick list of default values, set length limits or an edit mask for string parameters, or set range limits for number or date parameters. See Chapter 14 for more information on these options. You can even rename a stored procedure “based parameter in the Field Explorer the same way you would rename a regular parameter field (although this won t change the name of the parameter in the stored procedure).

When you refresh the report, you receive the same prompt as when using parameter fields.

You can choose whether to use the existing values for the stored procedure parameters or to prompt for new values. When you prompt for new values, the server will run the stored procedure with the new values and return a new result set to Crystal Reports.