I'm trying to loop over a range of dates using a stored procedure, then return the date and a calculated number for each date. However, I just get a timeout, even when I pass it a single date.
I'm kind of unsure how to debug this. My stored procedure is below:
ALTER PROCEDURE dbo.GetRoomVacancyCount
(
@RoomID int = -1,
@StartDate smalldatetime = GetDate,
@EndDate smalldatetime = GetDate,
@CustomerID uniqueidentifier = NULL
)
AS
SET NOCOUNT ON
DECLARE @numExcluded INT
DECLARE @numBooked INT
DECLARE @numInCart INT
DECLARE @numTotal INT
DECLARE @numAvailable INT
DECLARE @currentDate smalldatetime
/*** Create new Ones ***/
CREATE TABLE #TEMP(curDate SMALLDATETIME, Available int)
SET @currentDate = @StartDate
WHILE @currentDate <= @EndDate
BEGIN
SELECT @numTotal = Quantity FROM Rooms
SELECT @numExcluded = COUNT(*) FROM RoomExclusions WHERE ((StartDate >= @StartDate AND StartDate <= @EndDate) OR (EndDate >= @StartDate AND EndDate <= @EndDate)) AND RoomID = @RoomID
SELECT @numBooked = COALESCE(SUM(Quantity), 0) FROM bookingitems i WHERE ((StartDate >= @StartDate AND StartDate <= @EndDate) OR (EndDate >= @StartDate AND EndDate <= @EndDate)) AND RoomID = @RoomID
SELECT @numInCart = CASE @CustomerID
WHEN NULL THEN 0
ELSE COALESCE((SELECT SUM(Quantity) FROM Carts c WHERE ((StartDate >= @StartDate AND StartDate <= @EndDate) OR (EndDate >= @StartDate AND EndDate <= @EndDate)) AND RoomID = @RoomID AND CustomerID = @CustomerID), 0)
END
SET @numAvailable = @numTotal - @numExcluded - @numBooked - @numInCart
INSERT INTO #TEMP (curDate, Available) VALUES (@currentDate, @numAvailable)
END
SELECT * FROM #TEMP
/*** Tidy Up ***/
DROP TABLE #TEMP