Stored Proc - can this be done?

[jenlion]

Hi all, hoping someone can tell me if this is possible.

Crystal 10, SQL 2000.

I need to create a report that returns a price. There is a stored procedure in the DB that does this nicely for the GUI.

There are 11 input variables (some will be null, but several are relevant and I have to fill them) and 9 return values (I only care about one of them).

Is it possible to "feed" the procedure input variables? I would want my details section to list, for example, an Item (which is an input variable) and that item's price (the output variable for that procedure). Other input variables would be report-wide but still need to go to proc.

I've never tried to call a proc from Crystal before, so I don't have any idea if Crystal can do this or not. So, my questions are:

1. Can Crystal do this?
2. Point me to where to start reading about how?

Thanks for any help!

 

[Madawc]

Here's one way to add a Stored Procedure, assuming you have access to the code:

Within DataBase Expert, choose the database
Choose 'Add Command'. Open the worksheet
Paste the code from 'Query'. This includes comments etc.
The command can also be edited.

If you can run SQL on your machine, or have access to a machine that can, you could also do your own simplified version that gives just the data you want.

Once added, the fields are just like any other table.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[synapsevampire]

Not much to read, just point to the stored procedure (SP).

By default crystal does not show SPs. use File->Options->Database to turn on Stored Procedures.

The report will then prompt for the parameters and return the data just like a table would, unless there's something unusual in the SP.

-k

 

[mjhessler]

I recommend all reports use stored procedures instead of having SQL statements in Crystal.

Is anyone aware of a good way to export SQL statements in Crystal. We have over a thousand legancy Crystal reports to convert to stored procedures.

 

[Madawc]

mjhessler, that's a completely new topic, so please start a new thread.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods change between versions, and higher versions have extra options. SQL usage changed drastically between 8.5 and 10

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10