quote:

---

DECLARE @firstRow int
DECLARE @lastRow int
SELECT @firstRow = (@pageNumber - 1) * @itemsPerPage + 1,
@lastRow = (@pageNumber - 1) * @itemsPerPage + @itemsPerPage,
@totalRecords =
(SELECT COUNT(DISTINCT(t.[TrackID])) FROM Tracks t
INNER JOIN Artist a ON
a.ArtistID = t.ArtistID

INNER JOIN TrackOccasion tOcc ON
tOcc.TrackID = t.TrackID

WHERE a.ArtistName LIKE '%' + @keyword + '%'
AND tocc.OccasionID = @occasionID
);


WITH TracksQuery AS
(
SELECT a.ArtistName,
t.TrackID, t.TrackName, t.FullDuration, t.NonRegisteredPlaybackStartTime,
t.NonRegisteredPlaybackEndTime, t.RegisteredPlaybackStartTime,
t.RegisteredPlaybackEndTime, t.TrackDescription, t.TrackURL, t.GuidanceNotes, t.Religious, t.CatNo, t.Era, t.Tempo, t.Vocals,
t.[Year],
t.AdditionalInformation,
ROW_NUMBER() OVER (ORDER BY t.[TrackName], a.[ArtistName] ASC) AS RowNumber
FROM Tracks t

INNER JOIN Artist a ON
a.ArtistID = t.ArtistID

INNER JOIN TrackOccasion tOcc ON
tOcc.TrackID = t.TrackID

WHERE a.ArtistName LIKE '%' + @keyword + '%'
AND tocc.OccasionID = @occasionID
)

SELECT RowNumber, ArtistName,
TrackID, TrackName, FullDuration, NonRegisteredPlaybackStartTime,
NonRegisteredPlaybackEndTime, RegisteredPlaybackStartTime,
RegisteredPlaybackEndTime, TrackDescription, TrackURL, GuidanceNotes, Religious, CatNo, Era, Tempo, Vocals,
[Year], AdditionalInformation

FROM TracksQuery
WHERE RowNumber BETWEEN @firstRow AND @lastRow

---

quote:

---

DECLARE @itemsPerPage int
SET @itemsPerPage = 10
DECLARE @pageNumber int
SET @pageNumber = 1
DECLARE @totalRecords int

DECLARE @firstRow int
DECLARE @lastRow int
SELECT @firstRow = (@pageNumber - 1) * @itemsPerPage + 1,
@lastRow = (@pageNumber - 1) * @itemsPerPage + @itemsPerPage,
@totalRecords =
(SELECT COUNT(tmpCount.ControlNumber) FROM (
SELECT DISTINCT <fieldListHere>
FROM Control
<inner join statements>
<Left outer join statement>
WHERE (<first condition>) AND Manufacturer.MfrName LIKE @manufacturer
UNION
SELECT DISTINCT <fieldListHere>
FROM Control
<inner join statements here... 1 extra table than above>
<Left outer join statement>
WHERE (<first condition>)
AND Manufacturer.MfrName LIKE @manufacturer

) AS tmpCount);

WITH AllMyRecords AS (
SELECT DISTINCT <fieldListHere>
,ROW_NUMBER() OVER
(ORDER BY <fields> ASC) AS RowNumber
FROM Control
inner join statements>
<Left outer join statement>
WHERE (<first condition>) AND Manufacturer.MfrName LIKE @manufacturer
UNION
SELECT DISTINCT <fieldListHere>,
ROW_NUMBER() OVER
(ORDER BY <fields> ASC) AS RowNumber
FROM Control
<inner join statements here... 1 extra table than above>
<Left outer join statement>
WHERE (<first condition>)
AND Manufacturer.MfrName LIKE @manufacturer
)


SELECT * FROM AllMyRecords WHERE RowNumber BETWEEN @firstRow AND @lastRow
ORDER BY AllMyRecords.MfrName, AllMyRecords.ModelNumber ASC

---