-- 这个是通用的if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_PageView]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_PageView] GO/*--利用SQL未公开的存储过程实现分页方法简单且效率高,已知的问题就是要多返回一个空的记录集解决的方法是在前台调用时,用 set recordset=recordset.nextrecordset 的方法跳过第一个记录集此方法由J9988提供,我只是将它改成了方便调用的存储过程--邹建 2004.05(引用请保留此信息)--*//*--调用示例declare @PageCount int exec sp_PageView @sql='select * from sysobjects', @PageCurrent=2, @PageCount=@PageCount out SELECT @PageCount --*/ CREATE PROC sp_PageView @sql ntext, --要执行的sql语句 @PageCurrent int=1, --要显示的页码 @PageSize int=10, --每页的大小 @PageCount int OUTPUT --总页数 AS SET NOCOUNT ON DECLARE @p1 int --初始化分页游标 EXEC sp_cursoropen @cursor=@p1 OUTPUT, @stmt=@sql, @scrollopt=1, @ccopt=1, @rowcount=@PageCount OUTPUT--计算总页数 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 SET @PageCount=(@PageCount+@PageSize-1)/@PageSize IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount SET @PageCurrent=1 ELSE SET @PageCurrent=(@PageCurrent-1)*@PageSize+1--显示指定页的数据 EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize--关闭分页游标 EXEC sp_cursorclose @p1 GO
create PROCEDURE dbo.GetPagingRecord ( @tablename varchar(100),--表名或视图表 @fieldlist varchar(4000)='*',--欲选择字段列表 @orderfield varchar(100),--排序字段 @keyfield varchar(100),--主键 @pageindex int,--页号,从0开始 @pagesize int=20,--页尺寸 @strwhere varchar(4000),--条件 @ordertype bit=1--排序,1,降序,0,升序 ) AS /**//* 名称:GetPagingRecord 作用:按任意字段进行排序分页 作者:菩提树(MARK MA) 时间:2004-12-14 声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明 */ SET NOCOUNT ON declare @sqlstr varchar(6000) --处理SQL中危险字符,并且将条件处理成易嵌入的形式 set @strwhere=replace(@strwhere,'''','''''') set @strwhere=replace(@strwhere,'--','') set @strwhere=replace(@strwhere,';','') set @sqlstr='declare @CurPageNum int;' set @sqlstr=@sqlstr+'declare @nextpagenum int;' set @sqlstr=@sqlstr+'set @curpagenum='+cast(@PageIndex as varchar)+'*'+cast(@Pagesize as varchar)+';' set @sqlstr=@sqlstr+'set @nextpagenum='+cast(@PageIndex+1 as varchar)+'*'+cast(@Pagesize as varchar)+';' set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);' if @ordertype=1 begin set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from
'+@tablename+' where '+@strwhere+' order by '+@orderfield+' desc ) as a where '+@keyfield+' not in (
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'
order by '+@orderfield+' desc) order by '+@orderfield+' desc'';' end else begin set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from
'+@tablename+' where '+@strwhere+' order by '+@orderfield+' asc ) as a where '+@keyfield+' not in (
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'
order by '+@orderfield+' asc) order by '+@orderfield+' asc'';' end set @sqlstr=@sqlstr+'execute( @sqlstr)' --print @sqlstr execute(@sqlstr)
drop procedure [dbo].[sp_PageView]
GO/*--利用SQL未公开的存储过程实现分页方法简单且效率高,已知的问题就是要多返回一个空的记录集解决的方法是在前台调用时,用 set recordset=recordset.nextrecordset
的方法跳过第一个记录集此方法由J9988提供,我只是将它改成了方便调用的存储过程--邹建 2004.05(引用请保留此信息)--*//*--调用示例declare @PageCount int
exec sp_PageView
@sql='select * from sysobjects',
@PageCurrent=2,
@PageCount=@PageCount out
SELECT @PageCount
--*/
CREATE PROC sp_PageView
@sql ntext, --要执行的sql语句
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小
@PageCount int OUTPUT --总页数
AS
SET NOCOUNT ON
DECLARE @p1 int
--初始化分页游标
EXEC sp_cursoropen
@cursor=@p1 OUTPUT,
@stmt=@sql,
@scrollopt=1,
@ccopt=1,
@rowcount=@PageCount OUTPUT--计算总页数
IF ISNULL(@PageSize,0)<1
SET @PageSize=10
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount
SET @PageCurrent=1
ELSE
SET @PageCurrent=(@PageCurrent-1)*@PageSize+1--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize--关闭分页游标
EXEC sp_cursorclose @p1
GO
(
@tablename varchar(100),--表名或视图表
@fieldlist varchar(4000)='*',--欲选择字段列表
@orderfield varchar(100),--排序字段
@keyfield varchar(100),--主键
@pageindex int,--页号,从0开始
@pagesize int=20,--页尺寸
@strwhere varchar(4000),--条件
@ordertype bit=1--排序,1,降序,0,升序
)
AS
/**//*
名称:GetPagingRecord
作用:按任意字段进行排序分页
作者:菩提树(MARK MA)
时间:2004-12-14
声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明
*/
SET NOCOUNT ON
declare @sqlstr varchar(6000)
--处理SQL中危险字符,并且将条件处理成易嵌入的形式
set @strwhere=replace(@strwhere,'''','''''')
set @strwhere=replace(@strwhere,'--','')
set @strwhere=replace(@strwhere,';','')
set @sqlstr='declare @CurPageNum int;'
set @sqlstr=@sqlstr+'declare @nextpagenum int;'
set @sqlstr=@sqlstr+'set @curpagenum='+cast(@PageIndex as varchar)+'*'+cast(@Pagesize as varchar)+';'
set @sqlstr=@sqlstr+'set @nextpagenum='+cast(@PageIndex+1 as varchar)+'*'+cast(@Pagesize as varchar)+';'
set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);'
if @ordertype=1
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from
'+@tablename+' where '+@strwhere+' order by '+@orderfield+' desc ) as a where '+@keyfield+' not in (
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'
order by '+@orderfield+' desc) order by '+@orderfield+' desc'';'
end
else
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from
'+@tablename+' where '+@strwhere+' order by '+@orderfield+' asc ) as a where '+@keyfield+' not in (
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'
order by '+@orderfield+' asc) order by '+@orderfield+' asc'';'
end
set @sqlstr=@sqlstr+'execute( @sqlstr)'
--print @sqlstr
execute(@sqlstr)