sqlserver 存储过程中的top+变量使用分析(downmoon)
程序员文章站
2023-12-26 09:15:39
存储过程中的top后跟一个变量会如何? 复制代码 代码如下: create proc getworkplan2 (@intcounter int ,@lnguserid i...
存储过程中的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
邀月注:本文版权由邀月和博客园共同所有,转载请注明出处。
复制代码 代码如下:
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
邀月注:本文版权由邀月和博客园共同所有,转载请注明出处。