求高手帮我解决一个sql存储过程的问题
下面这个是我写的存储过程，检查语法是没问题的，也能实现分页查询。
不过有的时候，点分页的时候查询的结果好像是之前一个人搜的结果。
比如说：我搜“信息”的时候，别人之前搜了“sql”，搜出来的结果的前几页是与“信息”相关的信息，但分页的时候就可能出现与“sql "相关的信息，刷新一下就恢复正常。还有的时候就是出现 出错或者超时。
我想问题可能出现在我用表变量上。
请高手帮我解决这个难题，看看这个存储过程问题出在哪？
CREATE procedure InfoSearch
(@pagesize int,
@pageindex int,
@Keywords varchar(50),
@docount bit)
as
BEGIN TRAN
DECLARE @COUNT INT
DECLARE @XSid INT
set nocount on
if(@docount=1)
begin
IF((select count(XSid) from Xiaoshou as Xiaoshou1 inner join Com on Xiaoshou1.Uid=Com.Uid inner join FREETEXTTABLE(Xiaoshou,Title,@Keywords) as Xiaoshou2
on Xiaoshou1.XSid=Xiaoshou2.[KEY]) <21)
SET @COUNT=(select count(XSid) from Xiaoshou as Xiaoshou1 inner join Com on Xiaoshou1.Uid=Com.Uid inner join FREETEXTTABLE(Xiaoshou,Title,@Keywords) as Xiaoshou2
on Xiaoshou1.XSid=Xiaoshou2.[KEY])
ELSE
SET @COUNT=20
select count(XSid)+@COUNT from Xiaoshou as Xiaoshou1 inner join Com on Xiaoshou1.Uid=Com.Uid inner join containstable(Xiaoshou,Title,@Keywords) as Xiaoshou2
on Xiaoshou1.XSid=Xiaoshou2.[KEY]
end
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
BEGIN
insert into @indextable(nid) select top 1 XSid from Xiaoshou as Xiaoshou1 inner join Com on Xiaoshou1.Uid=Com.Uid
inner join containstable(Xiaoshou,Title,@Keywords) as Xiaoshou2 on Xiaoshou1.XSid=Xiaoshou2.[KEY] inner join db_User on Xiaoshou1.Uid=db_User.Uid
where db_User.Grade= '2 ' order by Xiaoshou1.Addtime desc
insert into @indextable(nid) select top 10 XSid from Xiaoshou inner join Com on Xiaoshou.Uid=Com.Uid inner join db_User on db_User.Uid=Xiaoshou.Uid
where Xiaoshou.Title=@Keywords and Grade= '2 ' and XSid in
(select max(XSid) from Xiaoshou inner join Com on Xiaoshou.Uid=Com.Uid inner join db_User on Xiaoshou.Uid=db_User.Uid
where Grade= '2 ' and Xiaoshou.Title=@Keywords group by ComName) and NOT EXISTS(SELECT nid from @indextable where nid=XSid)
order by Xiaoshou.AddTime desc
END
BEGIN
IF((SELECT COUNT(*) FROM Type WHERE TypeName=@Keywords)> 0)
BEGIN
SET @XSid = (SELECT TOP 1 ClasXSid FROM Type WHERE TypeName = @Keywords ORDER BY ClasXSid ASC)
insert into @indextable(nid) select top 20 XSid from Xiaoshou as Xiaoshou1 inner join Com on Xiaoshou1.Uid=Com.Uid

inner join containstable(Xiaoshou,Title,@Keywords) as Xiaoshou2 on Xiaoshou1.XSid=Xiaoshou2.[KEY] inner join db_User on Xiaoshou1.Uid=db_User.Uid
where db_User.Grade= '2 ' and BigType=@XSid and NOT EXISTS(SELECT nid from @indextable where nid=XSid) and Xiaoshou1.XSid in
(select max(Xs3.XSid) from Xiaoshou as Xs3 inner join Com on Xs3.Uid=Com.Uid
inner join containstable(Xiaoshou,Title,@Keywords) as Xs4 on Xs3.XSid=Xs4.[KEY] inner join db_User on Xs3.Uid=db_User.Uid
where Grade= '2 ' group by ComName)
OR db_User.Grade= '2 ' and SmallType=@XSid and NOT EXISTS(SELECT nid from @indextable where nid=XSid)
and Xiaoshou1.XSid in(select max(Xs3.XSid) from Xiaoshou as Xs3 inner join Com on Xs3.Uid=Com.Uid
inner join containstable(Xiaoshou,Title,@Keywords) as Xs4 on Xs3.XSid=Xs4.[KEY] inner join db_User on Xs3.Uid=db_User.Uid
where Grade= '2 ' group by ComName) order by Xiaoshou1.Addtime desc,Xiaoshou2.[RANK] desc,db_User.Credit desc
END
ELSE
BEGIN
insert into @indextable(nid) select top 20 XSid from Xiaoshou as Xiaoshou1 inner join Com on Xiaoshou1.Uid=Com.Uid
inner join containstable(Xiaoshou,Title,@Keywords) as Xiaoshou2 on Xiaoshou1.XSid=Xiaoshou2.[KEY] inner join db_User on Xiaoshou1.Uid=db_User.Uid
where db_User.Grade= '2 ' and NOT EXISTS(SELECT nid from @indextable where nid=XSid)
and Xiaoshou1.XSid in(select max(Xs3.XSid) from Xiaoshou as Xs3 inner join Com on Xs3.Uid=Com.Uid
inner join containstable(Xiaoshou,Title,@Keywords) as Xs4 on Xs3.XSid=Xs4.[KEY] inner join db_User on Xs3.Uid=db_User.Uid
where Grade= '2 ' group by ComName)order by Xiaoshou1.Addtime desc,Xiaoshou2.[RANK] desc,db_User.Credit desc
insert into @indextable(nid) select top 20 XSid from Xiaoshou as Xiaoshou1 inner join Com on Xiaoshou1.Uid=Com.Uid
inner join containstable(Xiaoshou,Title,@Keywords) as Xiaoshou2 on Xiaoshou1.XSid=Xiaoshou2.[KEY] inner join db_User on Xiaoshou1.Uid=db_User.Uid
where db_User.Grade= '2 ' and NOT EXISTS(SELECT nid from @indextable where nid=XSid)
and Xiaoshou1.XSid in(select max(Xs3.XSid) from Xiaoshou as Xs3 inner join Com on Xs3.Uid=Com.Uid
inner join containstable(Xiaoshou,Title,@Keywords) as Xs4 on Xs3.XSid=Xs4.[KEY] inner join db_User on Xs3.Uid=db_User.Uid
where Grade= '2 ' group by ComName)order by Xiaoshou1.Addtime desc,Xiaoshou2.[RANK] desc,db_User.Credit desc

insert into @indextable(nid) select XSid from Xiaoshou as Xiaoshou1 inner join Com on Xiaoshou1.Uid=Com.Uid
inner join containstable(Xiaoshou,Title,@Keywords) as Xiaoshou2 on Xiaoshou1.XSid=Xiaoshou2.[KEY] where NOT EXISTS(SELECT nid from @indextable where nid=XSid)
order by Xiaoshou1.Addtime desc,Xiaoshou2.[RANK] desc
insert into @indextable(nid) select top 20 XSid from Xiaoshou as Xiaoshou1 inner join Com on Xiaoshou1.Uid=Com.Uid inner join db_User on Xiaoshou1.Uid=db_User.Uid
inner join freetexttable(Xiaoshou,Title,@Keywords) as Xiaoshou2 on Xiaoshou1.XSid=Xiaoshou2.[KEY] where NOT EXISTS(SELECT nid from @indextable where nid=XSid)
order by Xiaoshou2.[RANK] desc,Credit desc,Xiaoshou1.Addtime desc
END
END

SELECT (SELECT ' <img src=../images/cxlm.gif> <br> '+CAST(Credit AS varchar(50)) From db_User WHERE db_User.Uid = O.Uid) AS Credit, ' <B>
<Font Class=title> <A Href=../Xiaoshou/ '+CAST(O.XSid AS varchar)+ '.html target=_blank> '+O.Title+ ' </A> </Font> </B> ' AS Title,O. Define,
' <BR> <a href= '+(SELECT Url FROM db_User WHERE db_User.Uid=Com.Uid)+ ' target=_blank> '+Com.ComName+ ' <font color=red> 拜访该公
司 </font> '+ ' </a> ' AS Shop,
' <Img width=65 height=65 src=../shop/sellimage/ '+ISNULL(O.ImgPath, 'Default.gif ')+ '> ' AS Img, LEFT(Com.Address,CHARINDEX( '市 ',Com.Address)) AS Adds,
O.Amount AS Amount
from Xiaoshou O,@indextable t ,Com
where O.XSid=t.nid
and t.id> @PageLowerBound and t.id <=@PageUpperBound and O.Uid=Com.Uid order by t.id

end
set nocount off
COMMIT TRAN
IF @@ERROR!=0
BEGIN
RAISERROR 2000 ' Cannot load data '
RETURN(1)
END
GO


[解决办法]
mark,帮顶,学习
[解决办法]
晕，太长了……实在无法看下去了
[解决办法]
你可不可以先建视图，不要满天尽是inner join