How can I get around this? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How can I get around this?

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2001-05-23 : 15:32:42

I have the following SP:

CREATE PROCEDURE spHotLaps_takeLap
(
@trackId int,
@page int,
@perPage int
)

AS

SET NOCOUNT ON

CREATE TABLE #tempHL
(
tempId int IDENTITY,
hotLapId int,
trackId int,
lapDateTime dateTime,
username varchar(30),
rating int,
comment text
)

INSERT INTO #tempHL (hotLapId, trackId, lapDateTime, username, rating, comment)

SELECT hotLapId, trackId, lapDateTime, username, rating, comment
FROM hotLaps, users
WHERE trackId = @trackId AND hotLaps.userId = users.userId
ORDER BY rating DESC

DECLARE @firstRec int, @lastRec int
SELECT @firstRec = (@page - 1) * @perPage
SELECT @lastRec = (@page * @perPage + 1)

SELECT trackName, trackCity, trackState, trackCountry, trackLength, trackStatus, trackWebsite, trackNumTurns, trackImage,
(
SELECT COUNT(*)
FROM #tempHL HL
WHERE HL.tempId >= @lastRec
) AS nextPage,

(
SELECT COUNT(*)
FROM #tempHL
) AS totalComments,

HL.*

FROM #tempHL HL RIGHT JOIN tracks ON HL.trackId = tracks.trackId
WHERE tracks.trackId = @trackId AND HL.tempId > @firstRec AND HL.tempId < @lastRec
GO

Now, no matter what, I need all the of the info requested from the table 'tracks'. The join works fine if there is at least one record in the temp table. However, if there are no records, then it doesn't return anything. If I drop the 'AND HL.tempId > @firstRec AND HL.tempId < @lastRec' then it will return everything fine. However, those conditions are needed so that it gets the appropriate records.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources