原来：
CREATE procedure GetPagedXWThird_ThesisBAK
(
@pagesize int,
@pageindex int,
@docount bit,
@whereStr nvarchar(1000))
as
set nocount on
if(@docount=1)
select count(thesisID) from thesis where thesisName like ‘%op%’
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 thesisID from thesis where thesisName like ‘%op%’ order by thesisID ASC
select thesisType,thesisName,pivotal,author,workplace,magazineName,wholeNO,thesisYear from thesis,@indextable t where thesisID=t.nid
and t.id> @PageLowerBound and t.id <[email protected] + @whereStr order by t.id
end
GO
条件 where thesisName like ‘%op%’位动态，[email protected]，如何更改？
------解决方案--------------------
CREATE procedure GetPagedXWThird_ThesisBAK
(
@pagesize int,
@pageindex int,
@docount bit,
@whereStr nvarchar(1000))
as
set nocount on
if(@docount=1)
select count(thesisID) from thesis where thesisName like '% '[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 thesisID from thesis where thesisName like '% '[email protected]+ '% ' order by thesisID ASC
select thesisType,thesisName,pivotal,author,workplace,magazineName,wholeNO,thesisYear from thesis,@indextable t where thesisID=t.nid
and t.id> @PageLowerBound and t.id <[email protected] + @whereStr order by t.id
end
GO
------解决方案--------------------
不能用表变量
CREATE procedure GetPagedXWThird_ThesisBAK
(
@pagesize int,
@pageindex int,
@docount bit,
@whereStr nvarchar(1000))
as
set nocount on
if(@docount=1)
exec ( 'select count(thesisID) from thesis where '+ @whereStr)
else
begin
exec( '
create table #indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=( '[email protected]+ '-1)* '[email protected]+ '
set @[email protected]+ '[email protected]+ '
set rowcount @PageUpperBound
insert into #indextable(nid) select thesisID from thesis where '+ @whereStr+ ' order by thesisID ASC
select thesisType,thesisName,pivotal,author,workplace,magazineName,wholeNO,thesisYear from thesis,@indextable t where thesisID=t.nid
and t.id> @PageLowerBound and t.id <[email protected] '+ @whereStr+ ' order by t.id
')
end
GO
------解决方案--------------------
terrence1106(曾阿牛) ( ) 信誉：99 2007-07-26 10:47:14 得分: 0
Haiwer(海阔天空) ，倒数第四行 还有个 ,@indextable ，换成#indextable 也不对；还多了两个where