Connecting to Stored Procedures
As mentioned earlier in the chapter, stored procedures can be used as a data source just like a table. Crystal Reports can open a stored procedure, retrieve the data and print it. The benefit of using stored procedures is that they let you use the SQL language to process complex logic that isn’t possible just by linking two tables together.
If your database has stored procedures but you don’t see them listed in the Database Expert dialog box, it’s probably because the option to view them has been disabled. To enable them, select the menu options File > Options and click on the Database tab. In the middle section, you’ll see a Stored Procedures checkbox. Check it to tell Crystal Reports to list them.
The one thing that can make retrieving data from a stored procedure unique is when it has input parameters. When a stored procedure uses input parameters, it has to be given a value for each parameter before it can run. Crystal Reports does this for you by automatically creating a new report parameter for every parameter in the stored procedure. It also gives the new report parameter the same name as the stored procedure parameter. So there is a one-to-one mapping of parameters between the stored procedure and the report and they have identical names and data types. When the report runs, the user is prompted to enter a value for each report parameter and Crystal Reports passes these parameter values to the stored procedure.
Let’s look at a sample stored procedure. The following code is a stored procedure from the Northwind database.
In this stored procedure, there is one input parameter called @CustomerID and it is a 5 character string. When this stored procedure is selected as the data source for a report, Crystal Reports automatically creates a report parameter called @CustomerID as a String data type. The report prompts the user to enter a Customer ID when it runs. The report passes this value to the stored procedure and the stored procedure only returns records with a matching ID.
