I've done some reading on this, and I keep seeing people say it's a bad idea, but hear me out!
I have a stored procedure which needs to find a series of timeslots over a number of days. Due to the fact that the timeslot count is the same for every date possible, they aren't duplicated for every date. Instead, it is recorded which timeslots are taken on what date. Those timeslots are then excluded from the query.
Due to the way the data is stored, I'm having to use a while loop to start on a given date, and query for a given number of days after. However, this is causing the return of multiple result sets. This would be OK, but the procedure will be called by Entity Framework, which does not support multiple result sets returning.
I've thought about storing it all in a temp table, but that option is out of the window due to a people problem: my dba is on holiday and I don't have an account with sufficient privileges to drop temp tables (or so it appears), and this needs doing before he'll get back.
Is there any way I can amalgamate all these result sets in to one? Every result set has the same structure.
EDIT TO ADD TABLE STRUCTURE (PKs in italics, becasue I can't find how to underline)
TimeSlotGroup UKTNN, TimeSlotGroupID, TimeSlotGroupDesc
TimeSlotGroupTimeSlots UKTNN, TimeSlotID, TimeSlotGroupID, TimeSlotDesc
BookedDates UKTNN, TimeSlotGroupID, TimeSlotID, Date
I'm returning currently:
ResultSet UKTNN, TimeSlotGroupID, TimeSlotID, TimeSlotGroupDesc, TimeSlotDesc, Date
It has duplicated data in it, but I'm OK with that right now
My existing query:
WHILE(@LoopDate <= DATEADD(DAY, @NumDays, @ArrivalDate)) BEGIN SELECT TSG.TimeSlotGroupID, TSG.TimeSlotGroupDesc, TS.TimeSlotID, TS.TimeSlotDescription, TS.AdditionalCost, @LoopDate Date, @UKTNN UKTNN FROM FittingCentreBranchTimeSlotGroups TSG, FittingCentreTimeSlotGroupTimeSlots TS WHERE TSG.UKTNN = @UKTNN AND TS.UKTNN = @UKTNN AND TS.TimeSlotGroupID = TSG.TimeSlotGroupID AND NOT EXISTS( SELECT * FROM BookedFittingDates BFD WHERE BFD.TimeSlotGroupID = TS.TimeSlotGroupID AND BFD.TimeSlotID = TS.TimeSlotID AND BFD.UKTNN = @UKTNN AND BFD.Date = @LoopDate) ORDER BY TimeSlotGroupID ASC, TS.TimeSlotID ASC SET @LoopDate = DATEADD(DAY, 1, @LoopDate) END