T-SQL: Stored proc with temporary table

Question

question

Echilon asked Apr 5, '10

T-SQL: Stored proc with temporary table

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

stored-procedures temporary-table loop

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-04-05T06:24:08Z

Fatherjack answered Apr 5, '10

I am away from a SQL server at the moment so cannot be exact on your syntax but where you set StartDate and EndDate you need to use GetDate().

Personally I would try to do this using a Tally table, described by Jeff Moden in this article http://www.sqlservercentral.com/articles/T-SQL/62867/, for all the reasons that Jeff describes in the article. If you convert to this process I dont think you will get any problems.

[Edit]
Sorry, been reading the code again and noticed there are a couple of other changes you need to make:
- the variable @currentdate is not incremented anywhere so you are not progressing through the date range supplied to the proc. this means the While test would always be true and the loop would never complete
- you are using a function name as a column name - curDate is an ODBC function. I dont think this will actually cause a problem but it would be worth renaming it just to rule it out

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-04-05T07:33:48Z

Kev Riley answered Apr 5, '10

As Fatherjack has pointed out, the reason the code has a timeout is that you aren't incrementing the @currentdate.

That aside - there are a lot more other issues with this code.
Eg. As it stands, you are looping for each date in the date range, but then counting the number of rows in the Exclusions table that match ANY of the dates, rather than the one you are currently processing. I am trying to rewrite this with one simple(r) query, but am struggling to understand the nature of some of the data.

So, you've got the answer to your original problem, but if you would like a little help re-writing this in the way that it should be, could you provide some sample data for the tables involved, and the expected output. The you can run your loop proc against the solutions we come up with, and I think you'll be pleasantly surprised!

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Echilon commented · Apr 05, 2010 at 02:27 PM

Thanks for the input. It's for a reporting control, but after benchmarking it, it's actually quicker for the proc to return one row (ie: no loop), then use prepared statements in .NET.

0 ·

question