sql2000分页存储过程 对照 2005分页存储过程

sql2000通用分页存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER   proc [dbo].[test_SelectByPage](
@name nvarchar(40),
@startRowIndex int,
@maximumRows int,
@SortBy nvarchar(100)
)
as

 

declare @strSQL   varchar(5000)       -- 主语句
declare @strTmp   varchar(110)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型
declare @sql nvarchar(3000)

declare @name_1 nvarchar(50)

create table #temp(
    id int identity(1,1),
   UID uniqueidentifier,
  
 name nvarchar(30)
  

)

begin transaction
begin
  --分页查询

begin
   Declare cur1 cursor for
   SELECT name FROM test WHERE  (name like isnull(@name,'')+'%')
    end
 
  Open cur1

     Fetch next from cur1 into @name_1
      while @@fetch_status = 0
   begin
    insert into #temp(UID,name)
select UID, name  FROM test WHERE name=@name_1

                  Fetch next from cur1 into @name_1

           end
  close cur1
  deallocate cur1

 

set @strTmp = '>(select max'
set @strOrder = ' order by id asc'

 

if (@startRowIndex = 0 )
 begin

 set @strSQL = 'select top ' + str(@maximumRows) +'  *   from #temp '+ @strOrder
 --如果是第一页就执行以上代码，这样会加快执行速度
 end

else
 begin
 set @strSQL = 'select top ' + str(@maximumRows) +'  *  from #temp
 where id' + @strTmp + '(id) from (select top ' + str(@startRowIndex) + ' id from #temp  ' + @strOrder + ') as tblTmp) '+ @strOrder
 end

exec (@strSQL)

Drop table #temp

 

 

  
end
IF @@ERROR > 0
   begin
    rollback transaction
   end
  commit transaction

 

2005分页存储过程

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER   proc [dbo].[test_SelectByPage](
@name nvarchar(40),
@startRowIndex int,
@maximumRows int,
@SortBy nvarchar(100)
)
as

 

 

begin transaction
begin
  --分页查询
    
     SELECT *
    FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY name DESC) AS rownum,
    *
    FROM test
    WHERE  (name like isnull(@name,'')+'%')
    )
    AS D
    WHERE rownum BETWEEN @startRowIndex+1 AND @startRowIndex+@maximumRows
   ORDER BY name DESC 

end
IF @@ERROR > 0
   begin
    rollback transaction
   end
  commit transaction