sql分页通用存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[UC_P_GETPAGEDDATA]
(
@Tables varchar(1000),
@PK varchar(100)=NUll,
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS

/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
 SET @Sort = @PK

/*Find the @PK type*/
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int

/*Set sorting variables.*/
IF CHARINDEX('DESC',@Sort)>0
 BEGIN
  SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
  SET @operator = '<='
 END
ELSE
 BEGIN
  IF CHARINDEX('ASC', @Sort) = 0
   SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
  SET @operator = '>='
 END

IF CHARINDEX('.', @strSortColumn) > 0
 BEGIN
  SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
  SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1,
LEN(@strSortColumn))
 END
ELSE
 BEGIN
  SET @SortTable = @Tables
  SET @SortName = @strSortColumn
 END

SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName

IF CHARINDEX('char', @type) > 0
   SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strFilter Nvarchar(1000)
DECLARE @strSimpleFilter Nvarchar(1000)
DECLARE @strGroup varchar(1000)
DECLARE @strStartRow varchar(50)
DECLARE @StrEndRow varchar(50)

/*Default Page Number*/
IF @PageNumber < 1
 SET @PageNumber = 1

/*Set paging variables.*/

SET @strStartRow =cast(((@PageNumber - 1)*@PageSize + 1) as varchar(50))
SET @StrEndRow=cast((@strStartRow+@PageSize-1) as varchar(50))

/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
 BEGIN
  SET @strFilter = ' WHERE ' + @Filter + ' '
  SET @strSimpleFilter = ' AND ' + @Filter + ' '

--print(@strFilter)
--print(@strSimpleFilter)
 END
ELSE
 BEGIN
  SET @strSimpleFilter = ''
  SET @strFilter = ''
 END
IF @Group IS NOT NULL AND @Group != ''
 SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
 SET @strGroup = ''
 
/*Execute dynamic query*/
--Exec(
--'
--DECLARE @SortColumn ' + @type + '
--SET ROWCOUNT ' + @strStartRow + '
--SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' +
--@strGroup + ' ORDER BY ' + @Sort + '
--SET ROWCOUNT ' + @strPageSize + '
--SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator +
--' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
--'
--)
Exec('
with TGetPagedData as
 (
  select '+@Fields+',ROW_NUMBER() over( order by '+@Sort+') as RowNumber from '+@Tables+@strFilter+'
 )
select * from TGetPagedData where RowNumber between '+@strStartRow+' and '+@StrEndRow+'
')

EXEC('select count(*) from '+ @Tables+ @strFilter)

--print ('
--with TGetPagedData as
-- (
--  select '+@Fields+',ROW_NUMBER() over( order by '+@Sort+') as RowNumber from '+@Tables+@strFilter+'
-- )
--select * from TGetPagedData where RowNumber between '+@strStartRow+' and '+@StrEndRow+'
--')

--print ('select count(*) from '+ @Tables+ @strFilter)

 

 

另一个不错的通用分页存储过程

===========================================================================================

 

GO
/****** 对象:  StoredProcedure [dbo].[GetRecordFromPage]    脚本日期: 01/29/2010 09:33:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER PROCEDURE  [dbo].[GetRecordFromPage] 'LogOnTable','*','useId',8,3,0,'desc'
          @tblName            varchar(255),                --   表名
          @RetColumns         varchar(1000) = '*',         --   需要返回的列，默认为全部  
          @Orderfld           varchar(255),                --   排序字段名  
          @PageSize           int  =  10,                  --   页尺寸  
          @PageIndex          int  =  1,                   --   页码  
          @IsCount            bit  =  0,                   --   返回记录总数,   非   0   值则返回  
          @OrderType          varchar(50)  =  'asc',       --   设置排序类型,   非   asc   值则降序  
          @strWhere           varchar(1000)  =  ''         --   查询条件   (注意:   不要加   where)  
  AS
     
  declare   @strSQL       varchar(1000)          --   主语句  
  declare   @strTmp       varchar(300)           --   临时变量  
  declare   @strOrder     varchar(400)           --   排序类型  
   
  if  @IsCount  !=   0    --执行总数统计
 
    begin
      if @strWhere != ''  
          set   @strSQL   =   'select   count(*)   as   Total   from   ['   +   @tblName   +   ']  where ' + @strWhere
      else
          set   @strSQL   =   'select   count(*)   as   Total   from   ['   +   @tblName   +   ']'
    end
   
  else                    --执行查询操作
 
  begin
           
 if  @OrderType  !=   'asc'  
  begin  
    set   @strTmp   =   '<(select   min'  
    set   @strOrder   =   '   order   by   ['   +   @Orderfld   +']   desc'  
  end  
 else  
  begin  
    set  @strTmp   =   '>(select   max'  
    set  @strOrder   =   '   order   by   ['   +   @Orderfld   +']   asc'  
  end  
  
 set  @strSQL   =   'select   top   '   +   str(@PageSize)   +   '   ' + @RetColumns + '    from   ['  
   +   @tblName   +   ']   where   ['   +   @Orderfld   +   ']'   +   @strTmp   +   '(['  
   +   @Orderfld   +   '])   from   (select   top   '   +   str((@PageIndex-1)*@PageSize)   +   '   ['  
   +   @Orderfld   +   ']   from   ['   +   @tblName   +   ']'   +   @strOrder   +   ')   as   tblTmp)'  
   +   @strOrder  
  
 if  @strWhere  !=   ''  
   set   @strSQL   =   'select   top   '   +   str(@PageSize)   +   '   ' + @RetColumns + '   from   ['  
     +   @tblName   +   ']   where   ['   +   @Orderfld   +   ']'   +   @strTmp   +   '(['  
     +   @Orderfld   +   '])   from   (select   top   '   +   str((@PageIndex-1)*@PageSize)   +   '   ['  
     +   @Orderfld   +   ']   from   ['   +   @tblName   +   ']   where   ('   +   @strWhere   +   ')   '  
     +   @strOrder   +   ')   as   tblTmp)   and   ('   +   @strWhere   +   ')   '   +   @strOrder  
  
 if  @PageIndex   =   1  
  begin  
    set   @strTmp   =   ''  
    if   @strWhere   !=   ''  
      set   @strTmp   =   '   where   ('   +   @strWhere   +   ')'  
   
    set   @strSQL   =   'select   top   '   +   str(@PageSize)   +   '   ' + @RetColumns + '   from   ['    
      +   @tblName   +   ']'   +   @strTmp   +   '   '   +   @strOrder  
  end
 end
   
exec  (@strSQL)