存储过程分页

USE UserGO/****** Object:  StoredProcedure [dbo].[sp_Pager2005]    Script Date: 2017/8/19 9:53:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[SP_CommonPager]@TableName varchar(255), -- 表名如：'xtest'@strGetFields varchar(1000) = '*', -- 需要返回的列如：'Name,EmpNo,Age'@strOrder varchar(255)='', -- 排序的字段名如：'order by id desc'@strWhere varchar(1500) = '', -- 查询条件(注意:不要加where)如:'Name like ''%Name%'''@pageIndex int = 1, -- 页码如：2@pageSize int = 10, -- 每页记录数如：10@recordCount int output, -- 记录总数@doCount bit=0 -- 0则不统计,非0则统计ASdeclare @strSQL varchar(5000)declare @strCount nvarchar(1000)--总记录条数if(@doCount!=0)beginif(@strWhere !='')beginset @strCount='set @num=(select count(1) from '+ @TableName + ' where '+@strWhere+' )'endelsebeginset @strCount='set @num=(select count(1) from '+ @TableName + ' )'endEXECUTE sp_executesql @strCount ,N'@num INT output',@RecordCount outputendif @strWhere !=''beginset @strWhere=' where '+@strWhereendset @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'set @strSQL=@strSQL+@strGetFields+' FROM ['+@TableName+'] '+@strWhereset @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)set @strSQL=@strSQL+'AND '+str(@PageIndex*@PageSize)exec (@strSQL)