日期:2014-05-19  浏览次数:20406 次

存储过程中这样使用为什么出错
declare   @aa   int
set   @aa=9
select   top   @aa   a.title   as   title,b.name   as   typename,
        a.publishdate   as   publishdate,a.replytimes   as   replytimes
from   diary   a
inner   join   diarytype   b   on   a.typeid=b.id
where   a.userid=0
order   by   a.publishdate   desc

提示:
Msg   102,   Level   15,   State   1,   Line   3
Incorrect   syntax   near   'aa '.

如果把@aa换为1就可以了

------解决方案--------------------
top 不支持这样的写法,把要执行的语句拼成字符串,用EXEC来执行。

参考:
http://www.cnblogs.com/happyhippy/archive/2007/01/29/633119.aspx
------解决方案--------------------
declare @aa int
declare @sql varchar(500)
set @aa=9
set @sql= 'select top '+@aa+ ' a.title as title,b.name as typename,a.publishdate as publishdate,a.replytimes as replytimes from diary a inner join diarytype b on a.typeid=b.id where a.userid=0 order by a.publishdate desc '
exec @sql
------解决方案--------------------
declare @aa int
set @aa=9
select top(@aa) a.title as title,b.name as typename,
a.publishdate as publishdate,a.replytimes as replytimes
from diary a
inner join diarytype b on a.typeid=b.id
where a.userid=0
order by a.publishdate desc
-----------------------------
不过要SQL2005