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