CREATE PROCEDURE [dbo].[usp_GetMovie] (
@offset INT
,@limit INT
)
AS
BEGIN
WITH MyCTE
AS (
SELECT T.MusicAlbumTitle
,D.musicTitle
,D.mVideoID
,D.musicFileName
,T.ReleaseDate AS
ReleasedDate
,D.MusicLength
,D.musicSinger
,D.MusicVideoID
,D.ExternalLink
,D.CoverImg
,ROW_NUMBER() OVER (
PARTITION BY D.MusicVideoID ORDER BY D.mVideoID
) AS row_num
FROM dbo.Music_Video T
JOIN dbo.Music_Video_Details D ON
T.MusicVideoID =
D.MusicVideoID
)
,countRow (CountRow)
AS (
SELECT max(row_num) AS CountRow
FROM MyCTE
)
SELECT r.CountRow
,c.*
FROM MyCTE c
,countRow r
where row_num>= @offset
AND row_num<= (@offset + @limit - 1)
END
ALTERNATIVE Calculating Count Separately at top.
CREATE PROCEDURE [dbo].[usp_GetMovie] (
@offset INT
,@limit INT
)
AS
BEGIN
DECLARE @CountRow INT
SELECT @CountRow = COUNT(MusicVideoID)
FROM dbo.Music_Video;
WITH MyCTE
AS (
SELECT T.MusicAlbumTitle
,D.musicTitle
,D.mVideoID
,D.musicFileName
,T.ReleaseDate AS
ReleasedDate
,D.MusicLength
,D.musicSinger
,D.MusicVideoID
,D.ExternalLink
,D.CoverImg
,ROW_NUMBER() OVER (
PARTITION BY D.MusicVideoID ORDER BY D.mVideoID
) AS row_num
FROM dbo.Music_Video T
JOIN dbo.Music_Video_Details D ON
T.MusicVideoID =
D.MusicVideoID
)
SELECT @CountRow AS CountRow
,c.*
FROM MyCTE c
,countRow r
where row_num>= @offset
AND row_num<= (@offset + @limit - 1)
END