CREATE procedure wqnews_GetSearchResult
(@skey NVarChar(100),
@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(newsid) from wqnews where [email protected]
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)[email protected]
set @[email protected][email protected]
set rowcount @PageUpperBound
insert into @indextable(nid) select newsid from wqnews where [email protected]
select O.newsid,O.heading,O.source,O.author,O.addtime from wqnews O,@indextable t where O.newsid=t.nid
and t.id> @PageLowerBound and t.id <[email protected]
end
set nocount off
GO
这样写的话就不出错
CREATE procedure wqnews_GetSearchResult
(@skey varChar(100),
@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
declare @strtemp varchar(100)
set @strtemp= 'heading '
if(@docount=1)
exec( 'select count(newsid) from wqnews where '[email protected]+ '= '[email protected])
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)[email protected]
set @[email protected][email protected]
set rowcount @PageUpperBound
exec( 'insert into @indextable(nid) select newsid from wqnews where '[email protected]+ '= '[email protected])
select O.newsid,O.heading,O.source,O.author,O.addtime from wqnews O,@indextable t where O.newsid=t.nid
and t.id> @PageLowerBound and t.id <[email protected]
end
set nocount off
GO
换成动态的就错了，[email protected]
------解决方案--------------------
exec( 'insert into @indextable(nid) select newsid from wqnews where '[email protected]+ '= '[email protected])
既然是字符串连接
就应该改成
exec( 'insert into @indextable(nid) select newsid from wqnews
where '[email protected]+ '= ' ' '+ @skey + ' ' ' ')
------解决方案--------------------
exec( 'select count(newsid) from wqnews where '[email protected]+ '= ' ' '[email protected]+ ' ' ') '
exec( 'insert into @indextable(nid) select newsid from wqnews where '[email protected]+ '= ' ' '[email protected]+ ' ' ') '