CREATE PROCEDURE [dbo].[up_Pager]
@table varchar(2000), –表名
@col varchar(50), –按该列来进行分页
@orderby bit, –排序,0-顺序,1-倒序
@collist varchar(800),–要查询出的字段列表,*表示全部字段
@pagesize int, –每页记录数
@page int, –指定页
@condition varchar(800) –查询条件
AS
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800),
@total_Item int,@total_Page int
IF @condition is null or rtrim(@condition)=”
BEGIN–没有查询条件
SET @where1=’ WHERE ‘
SET @where2=’ ‘
END
ELSE
BEGIN–有查询条件
SET @where1=’ WHERE (‘+@condition+’) AND ‘–本来有条件再加上此条件
SET @where2=’ WHERE (‘+@condition+’) ‘–原本没有条件而加上此条件
END
SET @sql=’SELECT @total_Item=CEILING((COUNT(*)+0.0)’+’) FROM ‘+@table+ @where2
EXEC sp_executesql @sql,N’@total_Item int OUTPUT’,@total_Item OUTPUT –计算总条数
set @total_Page = Ceiling((@total_Item+0.0)/@pagesize) –计算页总数
IF @orderby=0
SET @sql=’SELECT TOP ‘+CAST(@pagesize AS varchar)+’ ‘+@collist+
‘ , ‘+ CAST(@total_Item AS varchar) + ‘ as total_Item’ +
‘ , ‘+CAST(@total_Page AS varchar) + ‘ as total_Page’ +
‘ FROM mailto:’+@table+@where1+@col+’%3E(SELECT MAX(‘+@col+’) ‘+
‘ FROM (SELECT TOP ‘+CAST(@pagesize*(@page-1) AS varchar)+’ ‘+
@col+’ FROM ‘+@table+@where2+’ORDER BY ‘+@col+’) t) ORDER BY ‘+@col
ELSE
SET @sql=’SELECT TOP ‘+CAST(@pagesize AS varchar)+’ ‘+@collist+
‘ , ‘+ CAST(@total_Item AS varchar) + ‘ as total_Item’ +
‘ , ‘+CAST(@total_Page AS varchar) + ‘ as total_Page’ +
‘ FROM mailto:’+@table+@where1+@col+’%3C(select MIN(‘+@col+’) ‘+
‘ FROM (SELECT TOP ‘+CAST(@pagesize*(@page-1) AS varchar)+’ ‘+
@col+’ FROM ‘+@table+@where2+’ORDER BY ‘+@col+’ DESC) t) ORDER BY ‘+
@col+’ DESC’
IF @page=1–第一页
SET @sql=’SELECT TOP ‘+CAST(@pagesize AS varchar)+’ ‘+@collist+
‘ , ‘+ CAST(@total_Item AS varchar) + ‘ as total_Item’ +
‘ , ‘+CAST(@total_Page AS varchar) + ‘ as total_Page’ +
‘ FROM ‘+@table+
@where2+’ORDER BY ‘+@col+CASE @orderby WHEN 0 THEN ” ELSE ‘ DESC’ END
–print @sql
EXEC(@sql)
在SQL中测试(教你如何使用)
EXEC up_Pager ‘(SELECT * FROM 表名)aa’,’要排序的列名’,0-顺序或1-倒序,’显示列’,每页记录数,指定页,’条件’
EXEC up_Pager ‘(SELECT * FROM T_Gather_Page)aa’,’SaveTime’,1,’*’,40,3,”
原创文章，作者：1402239773，如若转载，请注明出处：https://blog.ytso.com/tech/database/233820.html