RE: Stored Procedure and Parameters

[allyne]

Hello,

I have a stored procedure with 2 parameters. @DateFrom and @DateTo. Normally I would execute this stored procedure in Query Analyzer (Exec UD 20090101,20091231)but I need to put this into a Crystal Report using an ODBC Connection for other users to run. the stored procedure has no output it creates tables and inserts data into those tables which is used in other crystal reports.

How can I pass these parameters into Crystal XII. I've tried to create an ODBC Connection but get a database connector error.

Any help would really be appreciated.

Thanks!

 

[kenhamady]

I don't think crystal can run an SP that has no output. For an SP to run in CR it has to meet some criteria (I don't remember all of them) but I am pretty sure it has to end with a dataset returned to CR. Can you end this SP with a small query?



Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[allyne]

Thanks for your quick response! I created a table and the output of this table say done. I used this as a select statement in my stored procedured as an output and it worked but it's now ignoring my parameters. Is there a way to pass the parameters into crystal reports?

Thanks for all your help!

 

[kenhamady]

If you refresh the SP from CR, and no SP parameters appear, then what did the SP use for parameter values when it ran?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[allyne]

I use @FromDate and @ToDate. The @FromDate uses a date format of 20091120 and the @FromDate uses the same format. What should happen is when the crystal report is refreshed it should prompt the user to enter the From and To dates. I see the parameters in the create parameters but because there is really no outcome I'm not seeing a way to select the paramaters. Any suggestions?

Thanks again for all your help!

 

[kenhamady]

Sorry, but you didn't answer my question. My question is what happens when you refresh the report that runs that SP.
If no parameters appear for you to fill in, then how is it able to run? What values is it using for those dates? Where does it get those values?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[kenhamady]

Also, can you add the parameters to the layout of the report so you can see the values?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[MzKitty]

Hi. Here's an example of a stored procedure that I am using that also uses a from and to date to populate a table that is created in the stored procedure.

CREATE PROCEDURE "GetReprintTkts"(in LocID varchar (10), in CustID varchar (10), in FromDate date, in ToDate date, in FromTrk varchar (15), in ToTrk varchar (15))

returns (TktNo int, TktUnqID int, TktDate date, TktLoc varchar (10), TktCust varchar (12),TktOrderID varchar (12), TktVehicleID varchar(12));

I didn't include all of the stored procedure, just the part dealing with the parameters and the info it returns. In crystal, you need to have the parameters spelled exactly the same as the stored procedure so that when the user is prompted to enter the from and to date, it can pass them to the procedure.

Good Luck
Cathy

 

[allyne]

Hello Everyone,

Thanks so much for all your help and suggestions! I was able to get it to work using Mzkitty's suggestion!

Thanks again!