求高手帮小弟我解决一个sql存储过程的有关问题
求高手帮我解决一个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
下面这个是我写的存储过程，检查语法是没问题的，也能实现分页查询。
不过有的时候，点分页的时候查询的结果好像是之前一个人搜的结果。
比如说：我搜“信息”的时候，别人之前搜了“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