SQL Server 2008 – Using UNION in Queries

querysql-server-2008

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

Best Answer

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.

Related Question