一个让人郁闷的存储过程

这是一个让人郁闷的分页存储过程,在存储过程中构造的sql语句形如
select top 10 * from [table] 结果只出来5条数据
如果是set rowcount 10 select * from [table] 会有10条数据
另一个郁闷的地方是,将构造的sql语句打印出来直接执行
不管是top 还是set rowcount 都可以正确的显示10行结果

--create procedure sp_loadannounce
declare @topicid int
declare @pagesize int
declare @pageindex int
--as
declare @boardid int
declare @sql nvarchar(4000)
declare @posttable nvarchar(20)

set @topicid=13808071
set @pagesize=10
set @pageindex=1

select @boardid=boardid,@posttable=PostTable from dv_topic where topicID=@topicid
set @sql='declare @boardid int
declare @topicid int
declare @pagesize int
declare @pageindex int
declare @recordCount int
declare @AnnounceID int
declare @rowcount int
set @boardid='+convert(nvarchar,@boardid)+'
set @topicid='+convert(nvarchar,@topicid)+'
set @pagesize='+convert(nvarchar,@pagesize)+'
set @pageindex='+convert(nvarchar,@pageindex)+'
select @recordCount=count(AnnounceID) from '+@posttable+' where boardid=@boardid and rootid=@topicid
set @rowcount=@pagesize*(@pageindex-1)
if @recordCount=0 begin
 select B.AnnounceID,B.BoardID,B.UserName,B.Topic,B.dateandtime,B.body,B.Expression,B.ip,B.RootID,B.signflag,B.isbest,B.PostUserid, B.layer,b.isagree,U.useremail,U.UserIM,U.UserMobile,U.Usersign,U.userclass,U.Usertitle,U.Userwidth,U.Userheight,U.UserPost, U.Userface,U.JoinDate,U.userWealth,U.userEP,U.userCP,U.Userbirthday,U.Usersex,u.UserGroup,u.LockUser,u.userPower,U.titlepic, U.UserGroupID,U.LastLogin,B.PostBuyUser,U.UserHidden,U.IsChallenge,B.Ubblist,B.LockTopic
 From '+@posttable+' B Inner Join [dv_user] U On U.UserID=B.PostUserID WHERE 1=2
end else if @rowcount<=0 begin
--set rowcount 10 --错误出在这儿如果使用set rowcount 10 就结果正确,如果使用top 10只出5行数据,郁闷ing
 select top '+Convert(nvarchar,@pagesize)+' B.AnnounceID,B.BoardID,B.UserName,B.Topic,B.dateandtime,B.body,B.Expression,B.ip,B.RootID,B.signflag,B.isbest,B.PostUserid, B.layer,b.isagree,U.useremail,U.UserIM,U.UserMobile,U.Usersign,U.userclass,U.Usertitle,U.Userwidth,U.Userheight,U.UserPost, U.Userface,U.JoinDate,U.userWealth,U.userEP,U.userCP,U.Userbirthday,U.Usersex,u.UserGroup,u.LockUser,u.userPower,U.titlepic, U.UserGroupID,U.LastLogin,B.PostBuyUser,U.UserHidden,U.IsChallenge,B.Ubblist,B.LockTopic
 From '+@posttable+' B Inner Join [dv_user] U On U.UserID=B.PostUserID
 Where B.RootID=@topicid And B.BoardID=@boardid Order BY B.AnnounceID, B.DateAndTime
end
else begin
 if @rowcount>@recordCount/@Pagesize*@pagesize begin
  set @rowcount=@recordCount/@Pagesize*@pagesize
 end
 set rowcount @rowcount
 select @AnnounceID=AnnounceID from '+@posttable+' where RootID=@topicid And BoardID=@boardid order by AnnounceID
 set rowcount '+convert(nvarchar,@pagesize)+'
 select B.AnnounceID,B.BoardID,B.UserName,B.Topic,B.dateandtime,B.body,B.Expression,B.ip,B.RootID,B.signflag,B.isbest,B.PostUserid, B.layer,b.isagree,U.useremail,U.UserIM,U.UserMobile,U.Usersign,U.userclass,U.Usertitle,U.Userwidth,U.Userheight,U.UserPost, U.Userface,U.JoinDate,U.userWealth,U.userEP,U.userCP,U.Userbirthday,U.Usersex,u.UserGroup,u.LockUser,u.userPower,U.titlepic, U.UserGroupID,U.LastLogin,B.PostBuyUser,U.UserHidden,U.IsChallenge,B.Ubblist,B.LockTopic
 From '+@posttable+' B Inner Join [dv_user] U On U.UserID=B.PostUserID
 Where B.RootID=@topicid And B.BoardID=@boardid and AnnounceID>@AnnounceID
 Order BY B.AnnounceID
end'
print(@sql)  ---比这个更郁闷的是如果直接执行这儿打印出来的sql语句,那么不管是使用set rowcount 10还是top 10都是相同的10行结果
exec(@sql)