转,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)