Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-06-01 : 11:15:51
|
Fabrizio writes "This will be a long question, i hope you'll be patient !
Let's say i have an application that work similar to Outlook Calendar.
Needing to handle recursive appointments without inserting 365 rows for an appointment repeating everyday for an year, i handled some flags to store the kind of recursion requested.
So the appointment set on 1° Jan repeating every day will have on the appointment table the int EveryNDays set to 1 and the stored procedure will be like:
pp_GetAppointments(@Date datetime) AS
SELECT @Date, Appointments.* FROM Appointments WHERE
DateDiff(d,Appointments.Date, @Date) % EveryNDays=0
this work fine.
The matter comes when i need to get the appointments from a range of dates.
In this case i have to send two data to the stored procedure, and execute the select shown before n times from Starting date to end date, like this:
pp_GetAppointments(@StartDate datetime, @EndDate datetime) AS
DECLARE @CurDate datetime
SET @CurDate = @StartDate
WHILE @CurDate <= @EndDate
SELECT @Date, Appointments.* FROM Appointments WHERE
DateDiff(d,Appointments.Date, @CurDate) % EveryNDays=0
SET @CurDate=DateAdd(d,1,@CurDate)
WEND
It also work fine, but returns as many recordset as the number of loop executed, making the application code like spaghetti when handling this data inside other requests.
Is there any way to get just one result with all the rows in?
Something like storing the single result of the select in memory and at the end returning all them in a single set ?
Hoping to stump,
Fabrizio" |
|
|
|
|
|
|
|
Multiple SELECT in a Stored Proc. returned as one set