转,sql的select top 变量

/*
* 说明：在sql server 2005中，由于支持select top 变量，因此，不需要用动态sql来实现
*        而在sql server 2000中，不支持select top 变量，因此，必须用动态sql
*/

declare @pageSize int--页数大小
declare @pageIndex int--页码
set @pageSize = 10
set @pageIndex = 10

--一、在sql server 2005中：
select * from (
     select top (@pageSize) name
     from (
       select top (@pageSize*@pageIndex) name from sysobjects order by name
       ) as a
    order by name desc
    ) as b
order by name

--二、在sql server 2000中：
declare @sqlStr varchar(1000)--动态sql
set @sqlStr = 'select * from (' +
     ' select top ' +convert(varchar,@pageSize) +' name' +
     ' from (' +
       ' select top ' +convert(varchar,@pageSize*@pageIndex) +' name from sysobjects order by name' +
       ' ) as a' +
    ' order by name desc' +
    ' ) as b' +
' order by name'

exec(@sqlStr)