关于存储过程传参数的
0
悬赏园豆：10
[已关闭问题]
<P>我建了个存储过程</P>
<P>if exists(select * from sysobjects where name='Proc_GetSnm_announcementByTopSize')<BR> drop proc Proc_GetSnm_announcementByTopSize<BR>go<BR>create proc Proc_GetSnm_announcementByTopSize<BR> @size int<BR>as<BR>declare @sql nvarchar(4000)<BR> if(@size=0)<BR> begin <BR> select top (select count(*) from snm_announcement) snm_announcement.*,snm_user.username <BR> from snm_announcement,snm_user <BR> where snm_announcement.uid=snm_user.uid <BR> order by pubdate desc<BR> end<BR> else select top @size) snm_announcement.*,snm_user.username <BR> from snm_announcement,snm_user <BR> where snm_announcement.uid=snm_user.uid <BR> order by pubdate desc'<BR> exec sp_executesql @sql<BR>go</P>
<P>老是报错 </P>
<P>@size 附近有语法错误 </P>
<P>这是怎么回事啊?</P>
其他回答(5)
0
我记得Select top 好象不能使用参数.
0
select top str(@size)
或者select top cast(int,@size)
cast的语法忘记了,楼主自己查一下
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
0
最好的办法是使用RowCount:
SET ROWCOUNT @Size; Select * from whatever; SET ROWCOUNT 0;
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上面；
0
else后面接着的select top @size)
少了一个左半括号，应该为：select top (@size)
sql2005以后可以用top加参数了
没用过别说不支持，尽量避免使用字符串拼接吧。
