继续上午的结帖问题：“sql2000查询分析器里运行此条语句出错，到底是哪错的泥？ ”

[中骑士]

上午问这个问题已经结帖给分，我以为得到了正确答案，但是回到我sql server 2000上怎么还会出错呢?

create procedure proc_GetListByPageIndex
(
	@pageIndex int=1,
	@pageSize int=5
)
as
	select top (@pageSize) * from employees where employeeid not in
	(
		select top ((@pageSize)*(@pageIndex-1)) employeeid from employees order by employeeid
	)order by employeeid

查询分析器里提示：
服务器: 消息 170，级别 15，状态 1，过程 proc_GetListByPageIndex，行 7
第 7 行: '(' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，过程 proc_GetListByPageIndex，行 9
第 9 行: '(' 附近有语法错误。

我结帖结早了，呵呵，重新发一下，再问！

[筱筱澄]

create procedure proc_GetListByPageIndex
(
    @pageIndex int=1,
    @pageSize int=5
)
as
    EXEC ('select top '+@pageSize+' * from employees where employeeid not in
    (
    select top '+@pageSize+'*('+@pageIndex+'-1)+employeeid from employees order by employeeid)order by employeeid'
    )

2000 不支持top 连变量的写法

要写成动态的

[answered]

declare @num int
set @num =10
DECLARE @sqlstr NVARCHAR(200)
SELECT @sqlstr = 'select top ('+CAST(@num AS VARCHAR(10)) + ') id from test '
EXEC(@sqlstr)

[筱筱澄]

[Quote=引用 1 楼 的回复:]

SQL code
create procedure proc_GetListByPageIndex
(
@pageIndex int=1,
@pageSize int=5
)
as
EXEC ('select top '+@pageSize+' * from employees where employeeid not in
(
select top '……
[/Quote]
抱歉这个写错了

create procedure proc_GetListByPageIndex
(
    @pageIndex int=1,
    @pageSize int=5
)
AS

DECLARE @sql VARCHAR(8000)
SELECT @sql ='select top '+rtrim(@pageSize)+' * from employees where employeeid not in
    (select top '+rtrim(@pageSize*(@pageIndex-1))+' employeeid from employees order by employeeid)
    order by employeeid
    '
EXEC (@sql)