CODEWITHSUNDEEP
c#.netstored-procedurescrystal-reports
Ahmed Atia
Ahmed Atia

Reputation: 17980

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?

Upvotes: 1

Views: 27009

Answers (5)

MrA
MrA

Reputation: 1

Pass some parameters from Crystal Report to DB packages.

Create new "Command" in Crystal Report with the same "Name" of parameter that existed in Crystal Report other "Command" and you can stored procedure normally.

For example: you were using "sum" function and received a result, and you want to stored proceduce to convert this number to "Reading way/Pronunciation" in your language, and in your DB was existed this Function before. Let's do it.

Upvotes: 0

Supitchaya
Supitchaya

Reputation: 511

EXEC dbo.StoredProcedure param1, param2 , ...

Not input parenthesis.

Upvotes: 0

Ahmed Atia
Ahmed Atia

Reputation: 17980

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.

Upvotes: 4

dotjoe
dotjoe

Reputation: 26940

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

Upvotes: 0

Maksym Gontar
Maksym Gontar

Reputation: 22775

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.

Upvotes: 3

Related Questions