lyfz_repos/经典版源码/document/数据库维护脚本/分页存储过程.sql

USE [db]
GO
/****** 对象:  StoredProcedure [dbo].[SP_PagesA]    脚本日期: 09/16/2013 13:03:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/* MSSQL数据分叶 */
ALTER PROCEDURE [dbo].[SP_PagesA]
(
  @Fields    	varchar(500),     	--要取出的字段，可以是多个表的字段，可以为空，为空表示select *
  @TableNames 	varchar(200),    	--表名，可以是多个表，但不能用别名
  @PrimaryKey 	varchar(100),   	--主键，可以为空，但@Order为空时该值不能为空
  @Filter 	varchar(500) = '',    	--条件，可以为空，不用填 where
  @Group 	varchar(200) = '',    	--分组依据，可以为空，不用填 group by
  @Order 	varchar(200) = '',    	--排序，可以为空，为空默认按主键升序排列，不用填 order by
  @PageSize 	int = 15,            	--每页记录数
  @CurrentPage 	int = 1        		--当前页，0表示第1页
)
AS
begin
  SET NOCOUNT ON
  declare @SortColumn varchar(200)
  declare @Operator char(2)
  declare @SortTable varchar(200)
  declare @SortName varchar(200)
  if @Fields = ''
      set @Fields = '*'
  if @Filter = ''
      set @Filter = 'where 1=1'
  else
      set @Filter = 'where ' + @Filter
  if @Group <>''
      set @Group = 'group by ' + @Group
  if @Order <> ''
  begin
      declare @pos1 int, @pos2 int
      set @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
      if CHARINDEX(' DESC', @Order) > 0
          if CHARINDEX(' ASC', @Order) > 0
          begin
              if CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
                  set @Operator = '<='
              else
                  set @Operator = '>='
          end
          else
              set @Operator = '<='
      else
          set @Operator = '>='
      set @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
      set @pos1 = CHARINDEX(',', @SortColumn)
      if @pos1 > 0
          set @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
      set @pos2 = CHARINDEX('.', @SortColumn)
      if @pos2 > 0
      begin
          set @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
          if @pos1 > 0 
              set @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
          else
              set @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
      end
      else
      begin
          set @SortTable = @TableNames
          set @SortName = @SortColumn
      end
  end
  else
  begin
      set @SortColumn = @PrimaryKey
      set @SortTable = @TableNames
      set @SortName = @SortColumn
      set @Order = @SortColumn
      set @Operator = '>='
  end
  declare @type varchar(50)
  declare @prec int
  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 @TopRows int
  set @TopRows = @PageSize * @CurrentPage + 1
  --print @TopRows
  --print @Operator
  exec('
      declare @SortColumnbegin ' + @type + '
      set RowCount ' + @TopRows + '
      select @SortColumnbegin=' + @SortColumn + ' from  ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' order by ' + @Order + '
      set RowCount ' + @PageSize + '
      select ' + @Fields + ' from  ' + @TableNames + ' ' + @Filter  + ' and ' + @SortColumn + '' + @Operator + '@SortColumnbegin ' + @Group + ' order by ' + @Order + '    
  ')
  SET NOCOUNT OFF
end