分页通用存储过程

/*通用分页存储过程1*/
USE HotelManagementSystem
GO
IF EXISTS(SELECT * FROM sys.objects WHERE NAME='cndoup_GetPageOfRecords')
  DROP PROCEDURE cndoup_GetPageOfRecords
GO
--创建存储过程
CREATE PROCEDURE cndoup_GetPageOfRecords

 

  @pageSize int = 20,                                                --分页大小
  @currentPage int ,                                                 --第几页
  @columns varchar(1000) = '*',                            --需要得到的字段 
  @tableName varchar(100),                                     --需要查询的表    
  @condition varchar(1000) = '',                         --查询条件, 不用加where关键字
  @ascColumn varchar(100) = '',                            --排序的字段名 (即 order by column asc/desc)
  @bitOrderType bit = 0,                                         --排序的类型 (0为升序,1为降序)
  @pkColumn varchar(50) = ''                                 --主键名称

AS
BEGIN                                                                                    --存储过程开始
  DECLARE @strTemp varchar(300)
  DECLARE @strSql varchar(5000)                            --该存储过程最后执行的语句
  DECLARE @strOrderType varchar(1000)                --排序类型语句 (order by column asc或者order by column desc)

  BEGIN
    IF @bitOrderType = 1                --降序
      BEGIN
        SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
        SET @strTemp = '<(SELECT min'
      END
    ELSE                  --升序
      BEGIN
        SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
        SET @strTemp = '>(SELECT max'
      END

    IF @currentPage = 1            --第一页
      BEGIN
        IF @condition != ''
          SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
            ' WHERE '+@condition+@strOrderType
        ELSE
          SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+@strOrderType
      END

    ELSE                  -- 其他页
      BEGIN
        IF @condition !=''
          SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
          ' WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+
          ' '+@pkColumn+' FROM '+@tableName'where'+@condition+@strOrderType+') AS TabTemp)'+@strOrderType
        ELSE
          SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
          ' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+' '+@pkColumn+
          ' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType
      END

  END
  EXEC (@strSql)
END  

 

                        --存储过程结束

 

--/*通用分页存储过程2--------源代码见附件中心---亲测好用/

USE [Db_TonyPaging]

GO

/****** Object:  StoredProcedure [dbo].[ZJF_CPP_GetPagedRecordFor2005_2008]    Script Date: 08/08/2013 10:37:37 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[ZJF_CPP_GetPagedRecordFor2005_2008]

(@Table varchar(1000), --表名,多表是请使用 tA a inner join tB b On a.AID = b.AID

@TIndex nvarchar(100),    --主键，可以带表头 a.AID

@Column nvarchar(2000) = '*',--读取字段

@Sql nvarchar(3000) = '',--Where条件

@PageIndex int = 1,    --开始页码

@PageSize int = 10,        --页大小

@Sort nvarchar(200) = '' --排序字段

)

AS

 

DECLARE @strWhere varchar(2000)

declare @strsql nvarchar(3900)

IF @Sql IS NOT NULL AND len(ltrim(rtrim(@Sql)))>0

  BEGIN

   SET @strWhere = ' WHERE ' + @Sql + ' '

  END

ELSE

  BEGIN

   SET @strWhere = ''

  END

        

if (charindex(ltrim(rtrim(@TIndex)),@Sort)=0)

begin

if(@Sort='')

set @Sort = @TIndex + ' DESC '

else

set @Sort = @Sort+ ' , '+@TIndex + ' DESC '

end

IF @PageIndex < 1

  SET @PageIndex = 1

 

if @PageIndex = 1 --第一页提高性能

begin 

 set @strsql = 'select top ' + str(@PageSize) +' '+@Column+ '  from ' + @Table + ' ' + @strWhere + ' ORDER BY  '+ @Sort

end 

else

 begin

 /**//**//**//*Execute dynamic query*/    

DECLARE @START_ID nvarchar(50)

DECLARE @END_ID nvarchar(50)

SET @START_ID = convert(nvarchar(50),(@PageIndex - 1) * @PageSize + 1)

SET @END_ID = convert(nvarchar(50),@PageIndex * @PageSize)

set @strsql =  ' SELECT '+@Column+ '

  FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum, 

'+@Column+ '

 FROM '+@Table +' WITH(NOLOCK) ' + @strWhere +') AS D

  WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort

 END

EXEC(@strsql)

print @strsql

set @strsql = 'SELECT  Count(1) as TotalRecords FROM ' + @Table +' WITH(NOLOCK) ' + @strWhere  

print @strsql

EXEC(@strsql)