存储过程中的top+变量

存储过程中的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' 附近有语法错误。于是改为

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 '
)

OK!

后来，经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  

 

转载于:https://www.cnblogs.com/downmoon/archive/2007/12/29/1019686.html