通用分页存储过程SQL

CREATE Procedure proc_Paged (
 @Tables nvarchar(4000), /*查询的表*/
 @PageIndex int=1,/*传进页数*/
 @PageSize int=10,/*每页行数*/
 @GetField nvarchar(3000),
 @OrderField nvarchar(200),
 @WhereClause nvarchar(max),
 @RecordCount int output )
AS
 Set NoCount ON--如果返回记录集，用这句话是个好习惯
   DECLARE @sql nvarchar(max) /*判断页数*/
   --IF(@PageIndex<0)  Set @PageIndex=0 /*判断行数*/
   --IF(@PageSize<1) set @PageSize=10
   IF (@WhereClause<>'')
    SET @WhereClause=' AND ('+@WhereClause+')'--设置条件
    SET @sql = 'SELECT @RecordCount= COUNT(*) FROM '+@Tables +' WHERE 1=1 '+@WhereClause
    exec sp_executesql @sql,N'@RecordCount int output',@RecordCount output
    Set @sql=N'Select * From (Select ROW_NUMBER() Over(ORDER BY '+@OrderField+N') AS ROWNUMBER,'+@GetField+' FROM '+@Tables+N' WHERE (1>0)'+@WhereClause+N' )AS PAGETABLE Where PAGETABLE.ROWNUMBER BETWEEN @p0+1 AND @p0 + @p1'
 DECLARE @startIndex int
 SET @startIndex = @PageIndex*@PageSize
 print @sql
 exec sp_executesql @sql,N'@p0 int,@p1 int',@p0=@startIndex,@p1=@PageSize
GO