Need an example of stored procedure w/ CR8 with date paramters please

[dreman]

CR 8.0
If any have an example of using stored Procedures with CR8, stored procedure must contain 2 dates (mm/dd/yy) parameters.
Please advise, Thank you.

 

[rhinok]

You need to specify your database. Oracle stored procs are written very differently than SQL Server stored procs, for example.

 

[LMCRYER]

I have several of these - I use stored procedures and Crystal reports extensively...

give me more info on what you are looking for?

Do you want to use the dates in the stored procedure as parameters within the report? Are they going to be on the stored procedure side or on the report itself?

What is your back end? What are you trying to DO with the data?

LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[dreman]

Thanks for the response.
I am using SQL 7.0
What I would like to have to start with a simple stored procedure defined as follow:
select * from

where
invoice_date in <date_promtp1> to <date_prompt2>

I do not know:
a- how to define the stored procedure. how to write it.
b- how to define the dates parameters (promtp1 and prompt2)
Please advise, Thank you.
dré

 

[LMCRYER]

Ok, I am not in my office right now but I will send you a sample of some stuff tmw okay?

Basically, you will declare the dates as variables in the stored procedure, use them in the where statement and then
when you attach your crystal rpt to this stored proceure they will come in on your report as parameters.

If you write the stored procedure well you will not have to do anything except set your defaults for the parameters on the report side.

I ll be in touch tmw am.

LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[dreman]

LMC Thanks,
dré

 

[LMCRYER]

if you send me your email address I can send you some sample files tmw? rather than post them, since it is a lot...

LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[rhinok]

Following is a simple stored procedure:

CREATE PROCEDURE TEST_PROC (@START_DATE DATETIME, @STOP_DATE DATETIME) AS
SELECT
  *
FROM
  TABLE
WHERE
  INVOICE_DATE >= @START_DATE
  AND INVOICE_DATE <= @STOP_DATE

When you base your report on this procedure, you will be prompted for the parameter values. Just out of curiostiy, why are you using a stored procedure? Something this simple could easily be accomplished within Crystal Reports.

 

[dreman]

Rhinok:
Yes you are right it could be done easily from CR, but when I go to show SQL Query it shows me the dates values and not the parameters, which brings me to my next question.
What are the difference between using stored procedure and using crystal expert selection ?

Thank you

 

[rhinok]

Generally, I'd only use a Stored Procedure if it[ol][li]was easier to create the dataset I need and[/li][li]ran more efficiently than any other method[/li][/ol]Stored Procedures can be incredibly powerful if they're written correctly, but why bother if you can accomplish your goals in a simpler manner? To answer your question, comparing Crystal's Record Select Expert to a Stored Procedure is like comparing apples to fruit salad. Not only are they different, but one can be significantly more complex than the other.

I create a ton of highly-complex reports and I almost never have to use Stored Procedures. In fact, I based my first report (in over 5 years) on a SQL Server 6.5 Stored Procedure (which was basically a View with multiple unions) because I couldn't get around a combination of SQL Server 6.5 and CR 6 limitations in any other way.

Relative to your exact situation, why do you need to see the parameter in Show SQL Query? I assume that you mean the date values shown in the Where clause are the parameters you entered when you refreshed the report (which means they will change everytime you refresh the report)? Could you please post your Show SQL Query and your record selection criteria?

 

[rogar]

One argument for using stored procedures: If the table structure is changed by inserting or removing fields, or by changing the data type of fields, the report based on a stored procedure is more likely to be unaffected than a report based on a direct link to the tables. Granted, the report can and should be fixed with Verify Database, but if you don't know about the changes, you can get some very peculiar report results.

 

[Nuffsaid]

Ya gotta love this place.....

I just posted a question about stored procedures in the Data Access forum, CR and SQL7 Stored Procedures, thread766-260134 then started poking around the other forums to see &quot;What's Up&quot;. I came accross this thread and had to check it out.

Rhinok, thanks for the sample.
Lmcryer, Can I also take you up on the email offer?

Guess there's something to be said about &quot;Being in the right place at the right time!

Nuffsaid.

 

[LMCRYER]

Nuffsaid-

Send me your email address and I will send you some stuff tmw - I dont have the files here.



LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[dreman]

rhinok:
Thanks for the info. Your explanation clarified a lot of things.
Thank you.
dré