一个用临时表的分页方案-数据库

CREATE PROC Bmhd_User_GetConsumeByUserID
@UserID  INT,
@PageIndex  INT,
@PageSize  INT,
@RecordCount  INT
AS

DECLARE
@PageLowerBound INT,
@PageUpperBound INT,
@RowsToReturn INT

SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
SET @RowsToReturn = @PageSize*(@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

CREATE TABLE #PageIndex
(
 IndexID INT IDENTITY(1, 1) NOT NULL,
 OID INT
)

INSERT INTO #PageIndex(OID) SELECT ID FROM UserConsumeLog WHERE UID = @UserID ORDER BY ID DESC

CREATE TABLE #Result
(
 ID  INT,
 UID  INT,
 MediaType NVARCHAR(20),
 MediaName NVARCHAR(500),
 Money  INT,
 DoneTime DATETIME
)

INSERT INTO #Result
SELECT
 u.ID,
 u.UID,
 CASE MediaType
 WHEN 0 THEN '音乐'
 WHEN 1 THEN '电视'
 WHEN 2 THEN '电影'
 WHEN 3 THEN '卡通电影'
 ELSE '未知'
 END,

 ISNULL (CASE MediaType
 WHEN 0 THEN (SELECT TOP 1 s.SongName FROM Song s WHERE s.SongID = u.MediaID)
 WHEN 1 THEN (SELECT TOP 1 t.TvName + '-' + ltrim(str(tu.TvNumber)) FROM TV t, TVUrl tu WHERE u.MediaID = tu.TvUrlID AND t.TvID = tu.TvID)
 WHEN 2 THEN (SELECT TOP 1 m.MovieName+'-'+ltrim(str(mu.MovieNumber)) FROM Movie m, MovieUrl mu WHERE u.MediaID = mu.MovieUrlID AND m.MovieID = mu.MovieID)
 WHEN 3 THEN (SELECT TOP 1 cm.CartoonMovieName+'-'+ltrim(str(cmu.CartoonMovieNumber)) FROM CartoonMovie cm, CartoonMovieUrl cmu WHERE u.MediaID = cmu.CartoonMovieUrlID AND cm.CartoonMovieID = cmu.CartoonMovieID)
 ELSE '未知'
 END,'未知'),

 u.Money,
 u.DoneTime
FROM
 UserConsumeLog u,
 #PageIndex p
WHERE
 u.ID = p.OID AND
 p.IndexID > @PageLowerBound AND
 p.IndexID < @PageUpperBound

SELECT * FROM #Result ORDER BY ID DESC

IF @RecordCount < 0 BEGIN
 SELECT COUNT(*) FROM UserConsumeLog WHERE uid = @userid
END
GO