一个好的分页的存储过程

CREATE procedure [dbo].[proTableSelectPage]
(
 @Tables   varchar(1000),   --表名如testtable
 @PrimaryKey  varchar(100),   --表的主键,必须唯一性
 @Sort   varchar(200) = NULL, --排序字段如f_Name asc或f_name desc(注意只能有一个排序字段)
 @CurrentPage int = 1,    --当前页
 @PageSize  int = 10,    --每页大小
 @Fields   varchar(1000) = '*', --显示的字段列表,多个字段用,分割
 @Filter   varchar(1000) = NULL, --条件语句,不加where,如 f_id>3
 @Group   varchar(1000) = NULL, --分组字段
 @TotalPage  int OutPut    --返回总页数
)

--WITH ENCRYPTION ---加密存储

AS

    SET NOCOUNT ON
    Declare @intResult Int  

    Begin Tran

    DECLARE @sql nvarchar(4000)
  
    If @Filter is null or @Filter=''
 Begin
        --set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' +  @Tables
        set @Sql = 'select @intResult = count(*) from ' +  @Tables
 End
    Else
 Begin
        --set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' +  @Tables + ' where + ' + @Filter  
        set @Sql = 'select @intResult = count(*) from ' +  @Tables + ' where + ' + @Filter  
 End

    EXEC sp_executesql @sql,N'@intResult int OUTPUT',@intResult OUTPUT--计算总记录数
    select @TotalPage=CEILING((@intResult+0.0)/@PageSize)--计算总页数
  
  
    IF @Sort IS NULL or @Sort = ''
    SET @Sort = @PrimaryKey

    DECLARE @SortTable  VarChar(100)
    DECLARE @SortName  VarChar(100)
    DECLARE @strSortColumn VarChar(200)
    DECLARE @operator  Char(2)
    DECLARE @type   VarChar(100)
    DECLARE @prec   int
  
    IF CHARINDEX('DESC',@Sort)>0
 BEGIN
        SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
        SET @operator = '<='
 END
    ELSE
 Begin
  IF CHARINDEX('ASC', @Sort) > 0
  BEGIN
   SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
   SET @operator = '>='
  END
  ELSE  
  BEGIN
   SET @strSortColumn = @SORT
   SET @operator = '>='
  END
 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
 Begin
  SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
    End

    DECLARE @strPageSize  varchar(50)
    DECLARE @strStartRow  varchar(50)
    DECLARE @strFilter   varchar(1000)
    DECLARE @strSimpleFilter varchar(1000)
    DECLARE @strGroup   varchar(1000)
  
    IF @CurrentPage < 1
 Begin
  SET @CurrentPage = 1
    End

    SET @strPageSize = CAST(@PageSize AS varchar(50))
    SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
  
    IF @Filter IS NOT NULL AND @Filter != ''
 BEGIN
  SET @strFilter = ' Where ' + @Filter + ' '
  SET @strSimpleFilter = ' AND ' + @Filter + ' '
 END
 ELSE
 BEGIN
  SET @strSimpleFilter = ''
  SET @strFilter = ''
 END
  
 IF @Group IS NOT NULL AND @Group != ''
 Begin
  SET @strGroup = ' GROUP BY ' + @Group + ' '
 End
 ELSE
 Begin
  SET @strGroup = ''
    End

 set @sql = '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(@sql)
    print @sql

 If @@Error <> 0
 Begin
  RollBack Tran
  Return -1
 End
 Else
 Begin
  Commit Tran
  Return @intResult ---返回记录总数
 End

 

-------------------------------------------------------------