Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Can you add example source schema/data, the query you have currently and the resultset you're after. –  Mark Storey-Smith Aug 4 '11 at 11:44
    
Sure, editing my post now. –  AndyBursh Aug 4 '11 at 11:47
add comment

1 Answer

up vote 4 down vote accepted

A few points:

1 - This is indeed a bad idea.

2 - Your syntax should be updated. Using implicit JOINs is generally bad since it can lead to a cartesian product if you aren't careful.

3 - Have you looked into using a recursive CTE?

4 - It also looks to me like the row-by-row could be unneeded anyways. Your date isn't used anywhere in your actual query except to be passed as a field and checked against the subexpression.

5 - Does this return a single row per date?

6 - Finally, #temp tables are dropped when the connection is closed. You don't need to drop them manually. If you can create them, as long as you don't make another with the same name in the same connection you won't need to drop it manually.

share|improve this answer
    
No, it doesn't return a single row per date. It returns a single row per time slot. So if a time slot group has 5 time slots, there will be 5 rows, and 5 duplicated time slot group id and date fields. How do you mean "Your date isn't used anywhere"? With it being a bad idea, is there a better way of doing this type of query? Would it be worth me moving some of the logic in to my code, rather than the stored procedure? –  AndyBursh Aug 4 '11 at 12:36
    
@Andy - doing row-by-row operations in SQL is generally not a good course to take. That being said, a CTE should work fine, or restructuring the query entirely to use set based logic should as well. –  JNK Aug 4 '11 at 12:40
    
Thanks a lot, I'll take a look at CTEs, and perhaps drudge up some set logic memories! –  AndyBursh Aug 4 '11 at 12:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.