SQL Sever数据库分页—存储过程
-- 数据库分页
CREATE PROCEDURE GetSortedMovies ( @SortExpression NVarChar(100), @StartRowIndex INT, @MaximumRows INT ) AS
-- 创建一个临时表存储查询结果
CREATE TABLE #PageIndex ( IndexId INT IDENTITY (1,1) NOT NULL, RecordId INT )
-- 插入临时表
INSERT INTO #PageIndex (RecordId) SELECT Id FROM Movies ORDER BY CASE WHEN @SortExpression='Id' THEN Id END ASC, CASE WHEN @SortExpression='Id DESC' THEN Id END DESC, CASE WHEN @SortExpression='Title' THEN Title END ASC, CASE WHEN @SortExpression='Title DESC' THEN Title END DESC, CASE WHEN @SortExpression='Description' THEN Description END ASC, CASE WHEN @SortExpression='Description DESC' THen Description END DESC
-- 得到页数
SELECT Id,Title,Description FROM Movies INNER JOIN #PageIndex WITH (nolock) ON Movies.Id = #PageIndex.RecordId WHERE #PageIndex.IndexId > @StartRowIndex AND #PageIndex.IndexId < (@StartRowIndex + @MaximunRows + 1) ORDER BY #PageIndex.IndexId