【MSSql2000】sql分页

最新推荐文章于 2019-07-02 20:51:41 发布

csdou

最新推荐文章于 2019-07-02 20:51:41 发布

阅读量440

点赞数

分类专栏：代码收藏整理文章标签： sql object qq go

本文链接：https://blog.csdn.net/csdou/article/details/6965952

版权

代码收藏整理专栏收录该内容

56 篇文章 0 订阅

订阅专栏

if exists (select * from dbo.sysobjects where id = object_id(N＇[dbo].[GetPagingRecord]＇) and OBJECTPROPERTY(id, N＇IsProcedure＇) = 1)
drop procedure [dbo].[GetPagingRecord]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create  PROCEDURE dbo.GetPagingRecord
    ( --作者：冯养信  QQ:71102886    2007-10-28
        @tablename varchar(100),--表名或视图表 
        @fieldlist varchar(4000)=＇*＇,--欲选择字段列表 
        @orderfield varchar(100),--排序字段 
        @keyfield varchar(100),--主键 
        @pageindex int,--页号,从1开始 
        @pagesize int=10,--页尺寸 
        @strwhere varchar(4000),--条件 
        @ordertype varchar(4)=＇asc＇--排序,desc,降序,asc,升序 
    ) 
AS 

    SET NOCOUNT ON 
    declare @sqlstr varchar(6000) 
    declare @orderstr varchar(100) 
    if(@keyfield=@orderfield)
    begin
 set @orderstr = ＇＇
    end
    else
    begin
 set @orderstr = ＇,＇+@keyfield+＇ asc＇
    end
    if @pageindex<=0 
 set @pageindex=1
    --处理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-1 as varchar)+＇*＇+cast(@Pagesize as varchar)+＇;＇ 
    set @sqlstr=@sqlstr+＇set @nextpagenum=＇+cast(@PageIndex as varchar)+＇*＇+cast(@Pagesize as varchar)+＇;＇ 
    set @sqlstr=@sqlstr+＇declare @sqlstr varchar(6000);＇ 
   
    set @sqlstr=@sqlstr+＇set @sqlstr=＇＇select ＇+@fieldlist+＇ from ( select top ＇＇+cast(@nextpagenum as varchar)+＇＇ * from  
 
＇+@tablename+＇  where 1=1 ＇+@strwhere+＇ order by ＇+@orderfield+＇ ＇+@ordertype + @orderstr+＇ ) as a where 1=1 and ＇+@keyfield+＇ not in (  
 
select top ＇＇+cast(@curpagenum as varchar)+＇＇ ＇+@keyfield+＇ from ＇+@tablename+＇ where 1=1 ＇+@strwhere+＇  
 
order by ＇+@orderfield +＇ ＇+@ordertype + @orderstr+＇) order by ＇+@orderfield +＇ ＇+@ordertype+＇＇＇;＇ 

    set @sqlstr=@sqlstr+＇execute( @sqlstr)＇ 
    print @sqlstr 
execute(@sqlstr)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO