<!-- .lineBorderBlue1 { BORDER-TOP: #999999 1px groove; BORDER-BOTTOM: #999999 1px groove; BORDER-LEFT: #999999 1px groove; BORDER-RIGHT: #999999 1px groove; background-color:#efefef; } -->
存储过程中的TOP后跟一个变量会如何? |
Create
proc
getWorkPlan2
(@intCounter
int
,
@lngUserID
int)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
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
(
'
selectTop
'
+
convert
(
varchar
(
10
),
@intCounter
)
+
'
lngWorkID,strWorkName,strExecHumanName,strBeginDatefromworklistwherelngExecHumanID=
'
+
convert
(
varchar
(
10
),
@lngUserID
)
+
'
orderbylngWorkIDdesc
'
|
|
老是提示 在关键字 'convert' 附近有语法错误。
于是改为 |
ALTER
proc
getWorkPlan2
(@intCounter
int
,
@lngUserID
int)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
as
declare
@strCounter
varchar
(
10
)
set
@strCounter
=
convert
(
varchar
(
10
),
@intCounter
)
declare
@strUserID
varchar
(
10
)
set
@strUserID
=
convert
(
varchar
(
10
),
@lngUserID
)
)
exec
sp_executesql
(
'
selectTop
'
+
@strCounter
+
'
lngWorkID,strWorkName,strExecHumanName,strBeginDatefromworklistwherelngExecHumanID=
'
+
@strUserID
+
'
orderbylngWorkIDdesc
'
)
|
|
OK! |
后来,经 saucer(思归) 大哥提醒,发现可以用以下语句实现(sql2005/sql2008):
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
邀月注:本文版权由邀月 和CSDN共同所有,转载请注明出处。
助人等于自助! 3w@live.cn