带参数的存储过程，谢谢！
原来：
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)*@pagesize
set   @PageUpperBound=@PageLowerBound+@pagesize
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 <=@PageUpperBound   +     @whereStr     order   by   t.id
end
GO

条件   where   thesisName   like   ‘%op%’位动态，所以应该以参数的形式@whereStr传进去，如何更改？


------解决方案--------------------
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 '% '+@whereStr+ '% '
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
insert into @indextable(nid) select thesisID from thesis where thesisName like '% '+@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 <=@PageUpperBound + @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=( '+@pageindex+ '-1)* '+@pagesize+ '
set @PageUpperBound=@PageLowerBound+ '+@pagesize+ '
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 <=@PageUpperBound '+ @whereStr+ ' order by t.id
')
end
GO

------解决方案--------------------
terrence1106(曾阿牛) ( ) 信誉：99 2007-07-26 10:47:14 得分: 0


Haiwer(海阔天空) ，倒数第四行 还有个 ,@indextable ，换成#indextable 也不对；还多了两个where