你是不是少写了个逗号呀??? @sql='insert into #indextable(nid) select newsid from news ' +@findtext+ ' order by '+@orderfield+','+@ordertype
意思是要形成这样的: 'insert into #indextable(nid) select newsid from news where heading like '*g*' order by newsid desc'RIVER_GetPager:----------------------------------------------------------------------- ALTER procedure RIVER_GetPager (@pagesize int, @pageindex int, @docount bit, @findtext nvarchar(20), @orderfield nvarchar(20), @ordertype varchar(4)) as
if(@docount=1) select count(newsid) from news else begin create table #indextable(id int identity(1,1),nid int) declare @PageLowerBound int declare @PageUpperBound int declare @sql varchar(20)
set @PageLowerBound=(@pageindex-1)*@pagesize set @PageUpperBound=@PageLowerBound+@pagesize set rowcount @PageUpperBound set @sql='insert into #indextable(nid) select newsid from news ' +@findtext+ ' order by '+@orderfield+' '+@ordertype print @sql exec('insert into #indextable(nid) select newsid from news ' +@findtext+ ' order by '+@orderfield+' '+@ordertype) select O.* from news O,#indextable t where O.newsid=t.nid and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id end
这样调用:exec RIVER_GetPager @pagesize=1, @pageindex = 1, @docount =0, @findtext =' where heading like ''%g%''', @orderfield ='newsid', @ordertype = 'desc'
@sql='insert into #indextable(nid) select newsid from news ' +@findtext+ ' order by '+@orderfield+','+@ordertype
'insert into #indextable(nid) select newsid from news where heading like '*g*' order by newsid desc'RIVER_GetPager:-----------------------------------------------------------------------
ALTER procedure RIVER_GetPager
(@pagesize int,
@pageindex int,
@docount bit,
@findtext nvarchar(20),
@orderfield nvarchar(20),
@ordertype varchar(4))
as
if(@docount=1)
select count(newsid) from news
else
begin
create table #indextable(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
declare @sql varchar(20)
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
set @sql='insert into #indextable(nid) select newsid from news ' +@findtext+ ' order by '+@orderfield+' '+@ordertype
print @sql
exec('insert into #indextable(nid) select newsid from news ' +@findtext+ ' order by '+@orderfield+' '+@ordertype)
select O.* from news O,#indextable t
where O.newsid=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end