What route to follow, I need data passed to a Stored Procedure

[eo]

Hi

I hope I am in the correct forum.

I have a stored procedure which outputs in which Week of the year a date falls. But I am new to stored procedures, and thought that a whole set of data (a field from Crystal reports) can be passed to a stored procedure which will then return the result for each date???
Is my assumption incorrect, is reality that the user running the report, must specify the parameter as required by the stored procedure? In which case a SP is not the solution, because I do not want a user to pass 365 parameters one at a time, I would prefer the report to auto pass the 365 dates to the SP parameter, which will then return the week numbers.

Thanks

EO
Hertfordshire, England

 

[synapsevampire]

A field from Crystal Reports isn't very descriptive.

it sounds like you want a year, or close to it passed to the SP. if the SP requires a single day, then it will have to run 365 times to return the data that you want.

I would rewrite the SP to accept 2 date ranges.

Overall I think that you need to get some technical people involved in the design process.

Technical requiremetns are better expressed with:

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[fredp1]

It almost sounds like you just need a function. This can be a Crystal function/formulae, or its gets tricky or for efficientcy reasons, then a db function is also possible.

Explain how you want to use the report. If its a function/formulae, then it just gets used within a report.

If you can provide more detail as mentioned by synapsevampire, a solution will be found.


Cheers
Fred

 

[eo]

Hi

CR10
SQL Database/ ODBC

As I said before, I though you could use a CR output...for example:

Date
01/01/2005
02/01/2005
01/02/2005
03/03/2005
08/10/2005

and to out put a weeknumber related to that date, by using the whole Date output, irrespective of the number of instances of dates..The initial SP code looked like

Create Procedure sp_Get_week_no_1
       @ReportDate DateTime
AS
Select WK_Week_Num, 
       WK_Year
From MAGIC.PanAshLive.dbo.Etienne_Weeks
Where @ReportDate >= Wk_Start_Date and 
      @ReportDate <= Wk_End_Date

GO

I how now been made aware that a stored procedure parameter will only be able to handle one variable at a time, and not a full set of date like:
01/01/2005
02/01/2005
01/02/2005
03/03/2005
08/10/2005

We overcame this problem by doing a view with a >= and <= link, although I would have liked to rather use SP, as I am desperate to learn more about them and thought this provided a perfect opportunity.

Cheers,


EO
Hertfordshire, England