SQLsever分页存储过程(有返回总页数,传入表名与字段条件)

/*第一次执行添加存储过程CREATE*/
 /*第二次执行修改*/ALTER PROC [sq_juxinkeji001].[GetDataByPage]
 
@PageSize INT,/*页大小*/
@CurrentPage INT,/*当前页数*/
@TableName VARCHAR(50),/*表名*/
@PageCount INT OUTPUT,/*总页数*/
@PkName VARCHAR(50),/*主键*/
@term VARCHAR(50),/*排序*/
@whereListName VARCHAR(50)/*主条件*/
AS
DECLARE @TotalCount int
DECLARE @sqls   nvarchar(4000)
  set   @sqls='select   @a=count(*)   from  '+@TableName
  exec   sp_executesql   @sqls,N'@a   int   output',@TotalCount output/*--定义@a为输出变量,@num 获取变量
print @TotalCount
IF(@TotalCount%@PageSize<>0)
   SET @PageCount=@TotalCount/@PageSize+1
ELSE
   SET @PageCount=@TotalCount/@PageSize*/
 SET @PageCount=@TotalCount

 
DECLARE @sql VARCHAR(8000)
DECLARE @count INT
SET @count = (@CurrentPage - 1) * @PageSize
SET @sql = ''
SET @sql = 'SELECT  TOP '+ CONVERT(VARCHAR(5),@PageSize) +
' *   FROM '+ @TableName + ' WHERE '+@PkName+' NOT IN(SELECT TOP
'+CONVERT(VARCHAR(5),@count)+ ' ' + @PkName  +' FROM '+@TableName+'
'+' ORDER BY '+@term+')
'+@whereListName+'
  order by '+@term
PRINT @sql
print '表名为:'+@TableName+' 页大小为:'+CONVERT(VARCHAR(5),@PageSize)+'  当前页:'
+CONVERT(VARCHAR(5),@CurrentPage)+'  总条数:'+CONVERT(VARCHAR(5),@PageCount)
EXEC(@sql)

/*测试 DECLARE  @pageCountp  int


exec GetDataByPage  5 ,1,diary,@pageCountp out,'time ','id desc','and 1!=3'
select @pageCountp
*/