sqlserver 存储过程中的top+变量使用分析(downmoon)

存储过程中的top后跟一个变量会如何？

create proc getworkplan2
(@intcounter int
,@lnguserid int)
as
select top 5 lngworkid,strworkname,strexechumanname,strbegindate
from worklist where lngexechumanid= @lnguserid
order by lngworkid desc

现在想将这里的top　5 改为变量· top @intcounter
如下

alter proc getworkplan2
(@intcounter int
,@lnguserid int)
as 　
)
exec　sp_executesql ('select top '+convert(varchar(10),@intcounter)+' lngworkid,strworkname,strexechumanname,strbegindate from worklist where lngexechumanid= '
+convert(varchar(10),@lnguserid) +' order by lngworkid desc '

老是提示　在关键字 'convert' 附近有语法错误。
ok!
于是改为

alter proc getworkplan2
(@intcounter int
,@lnguserid int)
as
declare @strcounter varchar(10)
set @strcounter=convert(varchar(10),@intcounter)
declare @struserid varchar(10)
set @struserid=convert(varchar(10),@lnguserid)
exec　sp_executesql ('select top '+@strcounter+' lngworkid,strworkname,strexechumanname,strbegindate from worklist where lngexechumanid= '
+@struserid +' order by lngworkid desc '
)

后来，经saucer(思归)大哥提醒，发现可以用以下语句实现(sql2005/2008)：

alter proc getworkplan2
(
@intcounter int
,@lnguserid int
)
as
set rowcount @intcounter
select lngworkid,strworkname,strexechumanname,strbegindate
from worklist where lngexechumanid= @lnguserid
order by lngworkid desc

邀月注：本文版权由邀月和博客园共同所有，转载请注明出处。