How to call StoredProcedure from CrystalReport?

问题:

I'd like to call a Stored Procedure from a crystal report and assign retrieved value to a field in report?

Any suggestions?

回答1:

To call Stored Procedure from crystal report,

Set data source of report to Stored Procedure (DataBase Expert Wizard). That procedure must met thses requirement

1- You must create a package that defines the REF CURSOR (type of field that will be retrieved).

2- The procedure must have a parameter that is a REF CURSOR type. This is because CR uses this parameter to access and define the result set that the stored procedure returns.

3- The REF CURSOR parameter must be defined as IN OUT (read/write mode).

4- Parameters can only be input (IN) parameters. CR is not designed to work with OUT parameters.

5- The REF CURSOR variable must be opened and assigned its query within the procedure.

6- The stored procedure can only return one record set. The structure of this record set must not change, based on parameters.

7- The stored procedure cannot call another stored procedure.

回答2:

Try Database Expert -> (left tree)Current Connections -> Add Command

In Add Command To Report screen input something like:

EXEC dbo.StoredProcedure (param1, param2 ...)

In the same screen you can specify parameters for this query.

As a result, new data source, based at the query command, will be created. You can use it as an ordinary data source and place values of fields in the report area.

回答3:

Just add it like you would a table or view. Parameters (if any) will be added to your report.

回答4:

EXEC dbo.StoredProcedure param1, param2 , ...

Not input parenthesis.