|
<div class="codetitle"><a style="CURSOR: pointer" data="51224" class="copybut" id="copybut51224" onclick="doCopy('code51224')"> 代码如下:<div class="codebody" id="code51224">
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_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_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中测试(教你如何使用)
<div class="codetitle"><a style="CURSOR: pointer" data="55402" class="copybut" id="copybut55402" onclick="doCopy('code55402')"> 代码如下:<div class="codebody" id="code55402">
EXEC up_Pager '(SELECT FROM 表名)aa','要排序的列名',0-顺序或1-倒序,'显示列',每页记录数,指定页,'条件'
EXEC up_Pager '(SELECT FROM T_Gather_Page)aa','SaveTime',1,'*',40,3,''
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
