存儲過程中的TOP后跟一個變量會如何？
復(fù)制代碼 代碼如下:
Create proc getWorkPlan2
(@intCounter int
,@lngUserID int)
as
select Top 5 lngWorkID,strWorkName,strExecHumanName,strBeginDate
from worklist where lngExecHumanID= @lngUserID
order by lngWorkID desc
現(xiàn)在想將這里的Top 5 改為變量· Top @intCounter
如下
復(fù)制代碼 代碼如下:
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 '
老是提示 在關(guān)鍵字 'convert' 附近有語法錯誤。
OK!
于是改為
復(fù)制代碼 代碼如下:
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 '
)
后來，經(jīng)saucer(思?xì)w)大哥提醒，發(fā)現(xiàn)可以用以下語句實現(xiàn)(sql2005/2008)：
復(fù)制代碼 代碼如下:
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
邀月注：本文版權(quán)由邀月和博客園共同所有，轉(zhuǎn)載請注明出處。
