两分页存储过程

分页存储过程  (一)  (这个速度快点 建议用这个)

CREATE  PROCEDURE hhhb_PageView1
         @PageIndex INT,                             --页面索引,从datagrid中获取
         @PageSize  INT,                              --页面显示数量，从datagrid中获取
         @RecordCount INT OUT,                --返回记录总数
         @PageCount INT OUT,                   --返回分页后页数
         @strGetFields nvarchar(1000),         -- 需要查询的列 首尾要有空格
         @tableName nvarchar(500) ,           --表名称 首尾要有空格
         @ID nvarchar(100),                          --主键,（为表的主键）
         @strWhere  nvarchar(1000) ='',        -- 查询条件 (注意: 不要加 where)
         @sortName nvarchar(50) =' asc ' ,     --排序方式
         @orderName nvarchar(100)              --父级查询排序方式

AS
declare @countSelect nvarchar(2000) 
--设置统计查询语句
if len(@strWhere) =0
--如果没有查询条件
    begin
        set @countSelect=N'SELECT @CountRecord = COUNT(*)  FROM '+@tableName
    end
else
--否则
    begin
        set @countSelect=N'SELECT @CountRecord = COUNT(*)  FROM '+@tableName+' where '+@strWhere
    end
--执行并返回总数
exec sp_executesql @countSelect,N'@CountRecord int output',@RecordCount output
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)

SET NOCOUNT ON

DECLARE @SQLSTR NVARCHAR(3000)
--实际总共的页码小于当前页码 或者 最大页码
if @PageCount>=0
    --如果分页后页数大于0
    begin
        if @PageCount<=@PageIndex and  @PageCount>0   --如果实际总共的页数小于datagrid索引的页数
            --or @PageCount=1
            begin
                --设置为最后一页
    set @PageIndex=@PageCount-1
            end
        else if @PageCount<=@PageIndex and  @PageCount=0
            begin
                set @PageIndex=0;
            end
    end

IF @PageIndex = 0 OR @PageCount <= 1  --如果为第一页
    begin
        if len(@strWhere) =0
            begin
                SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+'  FROM  '+@tableName+' ORDER BY '+@orderName+@sortName
            end
        else
            begin
                SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+'  FROM  '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName
            end
    end
ELSE IF     @PageIndex = @PageCount - 1 --如果为最后一页           
    begin
        if len(@strWhere) =0
            begin
                SET @SQLSTR =N' SELECT '+@strGetFields+'  FROM '+@tableName+' where '+@ID+' not in  ( SELECT TOP '+STR(/*@RecordCount - */@PageSize * @PageIndex )+@ID+'  FROM  '+@tableName+'ORDER BY '+@orderName+@sortName+' )   ORDER BY '+@orderName+@sortName
            end
        else
            begin
                SET @SQLSTR =N' SELECT '+@strGetFields+'  FROM '+@tableName+' where '+@ID+' not in  ( SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+'  FROM  '+@tableName+' where '+@strWhere+'ORDER BY '+@orderName+@sortName+' )  and '+@strWhere+' ORDER BY '+@orderName+@sortName
            end
    end
ELSE                                                              --否则执行 
    begin
         if len(@strWhere) =0
            begin
                SET @SQLSTR =N' SELECT TOP  '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in  ( SELECT TOP '+STR( /*@RecordCount - */@PageSize * @PageIndex )+@ID+'  FROM  '+@tableName+' ORDER BY '+@orderName+@sortName+' )  ORDER BY '+@orderName+@sortName
            end
         else
            begin
                SET @SQLSTR =N' SELECT TOP  '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in  (SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+'  FROM  '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName+' )and '+@strWhere+'ORDER BY '+@orderName+@sortName
            end
    end

EXEC (@SQLSTR)
/*print (@SQLSTR)*/
set nocount off
GO

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

///////////////////////////////////////////////////////////////////////////////
分页存储过程  (二)  

       CREATE proc hhhb_PageView 
       @a_TableList Varchar(200),
       @a_TableName Varchar(30),
       @a_SelectWhere Varchar(500),
       @a_SelectOrderId Varchar(20),
       @a_SelectOrder Varchar(50),
       @a_intPageNo int,
       @a_intPageSize int,
       @RecordCount int OUTPUT
as
   /*定义局部变量*/
   declare @intBeginID         int
   declare @intEndID           int
   declare @intRootRecordCount int
   declare @intRowCount        int
   declare @TmpSelect          NVarchar(600)
   /*关闭计数*/
   set nocount on
  
   /*求总共根贴数*/

   select @TmpSelect = 'set nocount on;select @SPintRootRecordCount = count(*) from '+@a_TableName+' '+@a_SelectWhere
   execute sp_executesql
             @TmpSelect,
             N'@SPintRootRecordCount int OUTPUT',
             @SPintRootRecordCount=@intRootRecordCount OUTPUT

select @RecordCount = @intRootRecordCount

   if (@intRootRecordCount = 0)    --如果没有贴子，则返回零
       return 0
      
   /*判断页数是否正确*/
   if (@a_intPageNo - 1) * @a_intPageSize > @intRootRecordCount
      return (-1)

   /*求开始rootID*/
   set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize + 1
   /*限制条数*/

   select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;select @SPintBeginID = '+@a_SelectOrderId+' from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
   execute sp_executesql
             @TmpSelect,
             N'@SPintRowCount int,@SPintBeginID int OUTPUT',
             @SPintRowCount=@intRowCount,@SPintBeginID=@intBeginID OUTPUT

   /*结束rootID*/
   set @intRowCount = @a_intPageNo * @a_intPageSize
   /*限制条数*/

   select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;select @SPintEndID = '+@a_SelectOrderId+' from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
   execute sp_executesql
             @TmpSelect,
             N'@SPintRowCount int,@SPintEndID int OUTPUT',
             @SPintRowCount=@intRowCount,@SPintEndID=@intEndID OUTPUT

if @a_SelectWhere='' or @a_SelectWhere IS NULL
   select @TmpSelect = 'set nocount off;set rowcount 0;select '+@a_TableList+' from '+@a_TableName+' where '+@a_SelectOrderId+' between '
else
   select @TmpSelect = 'set nocount off;set rowcount 0;select '+@a_TableList+' from '+@a_TableName+' '+@a_SelectWhere+' and '+@a_SelectOrderId+' between '

if @intEndID > @intBeginID
   select @TmpSelect = @TmpSelect+'@SPintBeginID and @SPintEndID'+' '+@a_SelectOrder
else
   select @TmpSelect = @TmpSelect+'@SPintEndID and @SPintBeginID'+' '+@a_SelectOrder

   execute sp_executesql
             @TmpSelect,
             N'@SPintEndID int,@SPintBeginID int',
             @SPintEndID=@intEndID,@SPintBeginID=@intBeginID

   return(@@rowcount)
   --select @@rowcount

GO

////////////////////////////////////////////////////////////////////////////////////////////////