首页 新闻 搜索 专区 学院

关于存储过程传参数的

0
悬赏园豆:10 [已关闭问题]
<P>我建了个存储过程</P> <P>if exists(select * from sysobjects where name='Proc_GetSnm_announcementByTopSize')<BR>&nbsp;drop proc Proc_GetSnm_announcementByTopSize<BR>go<BR>create proc Proc_GetSnm_announcementByTopSize<BR>&nbsp;@size int<BR>as<BR>declare&nbsp; @sql nvarchar(4000)<BR>&nbsp;&nbsp;&nbsp; if(@size=0)<BR>&nbsp;&nbsp;&nbsp; begin <BR>&nbsp;&nbsp;select top (select count(*) from snm_announcement)&nbsp; snm_announcement.*,snm_user.username <BR>&nbsp;&nbsp;&nbsp;&nbsp; from snm_announcement,snm_user <BR>&nbsp;&nbsp;&nbsp;&nbsp; where snm_announcement.uid=snm_user.uid <BR>&nbsp;&nbsp;&nbsp;&nbsp; order by pubdate desc<BR>&nbsp;&nbsp;&nbsp; end<BR>&nbsp;&nbsp;&nbsp;&nbsp;else&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select top @size) snm_announcement.*,snm_user.username <BR>&nbsp;&nbsp;&nbsp;&nbsp; from snm_announcement,snm_user <BR>&nbsp;&nbsp;&nbsp;&nbsp; where snm_announcement.uid=snm_user.uid <BR>&nbsp;&nbsp;&nbsp;&nbsp; order by pubdate desc'<BR>&nbsp;&nbsp;&nbsp; exec sp_executesql @sql<BR>go</P> <P>老是报错 </P> <P>@size 附近有语法错误 </P> <P>这是怎么回事啊?</P>
yeyang的主页 yeyang | 菜鸟二级 | 园豆:418
提问于:2008-03-26 11:57
< >
分享
其他回答(5)
0
我记得Select top 好象不能使用参数.
沙加 | 园豆:3680 (老鸟四级) | 2008-03-26 12:15
0
select top str(@size) 或者select top cast(int,@size) cast的语法忘记了,楼主自己查一下
WEBBER | 园豆:2 (初学一级) | 2008-03-26 12:55
0
对,Select top不能用参数,把语句跟参数加成字符串,然后用EXEC()函数来执行 比如: CREATE PROCEDURE dbo.getUserName @intTop int=15 as declare @sql varchar(1000) set @sql = 'select Top ' @intTop ' * from dbo.[userinfo]' exec (@sql) go
pensir | 园豆:175 (初学一级) | 2008-03-26 23:31
0
最好的办法是使用RowCount: SET ROWCOUNT @Size; Select * from whatever; SET ROWCOUNT 0;
deerchao | 园豆:8167 (大侠五级) | 2008-03-27 00:59
0
应该是create proc Proc_GetSnm_announcementByTopSize @size int as declare @sql nvarchar(4000) if(@size=0) begin ..... else .. set @sql='select Top '+CAST(@size AS NVARCHAR)+' * FROM TABLE'; exec (@sql) go 因为你的@sql是NVARCHAR 所以要把int的@size转化成NVARCHAR再连接到@sql上面;
IamV | 园豆:180 (初学一级) | 2008-03-27 09:38
0
else后面接着的select top @size) 少了一个左半括号,应该为:select top (@size) sql2005以后可以用top加参数了 没用过别说不支持,尽量避免使用字符串拼接吧。
风海迷沙 | 园豆:4453 (老鸟四级) | 2008-03-27 12:15
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册