Connecting with 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 one thing that can make a stored procedure unique is if it has input parameters.

For a stored procedure to execute, it must have a value for every input parameter. Crystal Reports automatically creates report parameters when it sees that the stored procedure has one or more input parameters. There is one report parameter for every stored procedure parameter and they will have identical names and data types. When the report runs, the user is prompted to enter a value for each parameter. Internally, Crystal Reports passes these report parameters to the stored procedure.

The following code is a sample stored procedure from the Northwind database.

CREATE PROCEDURE CustOrderHist
@CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID

In this stored procedure, there is one input parameter called @CustomerId that is a 5 character string. When this report is selected as the data source for a report, Crystal Reports automatically creates a parameter called @CustomerID as a String data type. The report prompts the user to enter a Customer Id when it is ran. The report passes this value to the stored procedure; the stored procedure only returns records with a matching Id.

If you want your application to directly pass the parameters to the stored procedure, you have to set the parameter objects during runtime. The user won’t be prompted with the parameter dialog boxes and the report will run seamlessly. See Chapter 17 for the steps to set report parameter values during runtime.

If you are using two stored procedures and linking them together, make sure that the parameters have different names. When Crystal Reports creates parameters for each stored procedure, it doesn’t have the capability to create a new alias for the parameter names. Consequently, it will only create one report parameter with that name and it won’t know which stored procedure to assign the parameter to. Make sure that parameters use unique names.