更好用的分页存储过程

 

ALTER PROCEDURE [dbo].[pro_T_Area_SelectPagedDynamic]
 @PageSize int,  --页面大小
 @PageIndex int,  --第几页
 @WhereCondition nvarchar(500) = NULL,   --条件
 @OrderByExpression nvarchar(250) = NULL  --排序
AS

--SET NOCOUNT ON
--SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
with TempTBL as (
SELECT ROW_NUMBER() OVER (ORDER BY  Area )AS Row, * from [dbo].[T_Area]'
IF @WhereCondition IS NOT NULL AND LEN(@WhereCondition) > 0
BEGIN
 SET @SQL = @SQL + ' Where ' + @WhereCondition
End

 SET @SQL = @SQL +')
 SELECT * FROM TempTBL where Row between ' + Str((@PageIndex-1)*@PageSize+1) + ' and ' + Str((@PageIndex-1)*@PageSize+@PageSize)

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
 SET @SQL = @SQL + '
 ORDER BY
 ' + @OrderByExpression
END

EXEC sp_executesql @SQL

 

这里引入了临时表来解决分页处理,服务器上的内存一般都比较大,因此可以容纳临时表处理