分页通用存储过程

/*通用分页存储过程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)