存儲過程分頁

create  procedure TurnPage
(
@ContainerNo varchar(16),
@isFilter smallint,
@PageSize int,
@CurrPage int,
@isCheck smallint,  --- check valid result
@ResultTotal int output,
@ResultSub int output,
@SortOrder varchar(10)
)
As
Begin
  declare  @topnum int,@previous int
  declare @ContPrefix varchar(6)
  Select  @ContPrefix=dbo.LLL_GetContainerPrefix(@ContainerNo)
  --select @PageSize = 30
  --select @CurrPage = 2
  declare @i int,@IDStr nvarchar(4000),@strSQL nvarchar(4000)
  --declare @strCountSQL nvarchar(1000)
  create table #wtemp(
   ContainerNo varchar(16) ,   
   BoxNo varchar(16),
   
   so varchar(80) NULL,
   PackageNo varchar(10) NULL,
   nw real NULL,
   gw real NULL,
   Contents varchar(120) NULL
  )    
  --print 'AAAAAAAAAA'
--  If(Left(@ContainerNo,1) = 'N' Or Left(@ContainerNo,3) = 'LLC')
  If @ContPrefix='Nor' or @ContPrefix='LLC'
   Begin
     insert   #wtemp SELECT distinct ContainerNo,BoxNo,wo,PackageNo,nw,gw,Contents  
     FROM ssItemNoBox Where ContainerNo = @ContainerNo order by BoxNo
   End
  Else
   Begin
     insert  #wtemp  SELECT distinct  ContainerNo,BoxNo,wo,PackageNo,nw,gw,Contents   
       FROM ssOtherItemNoBox Where ContainerNo = @ContainerNo  order by BoxNo
   End
  
  select @topnum = @CurrPage * @PageSize
  select @previous = (@CurrPage - 1) * @PageSize
  
  select @i = 0
  select @strSQL = N''
  select @strSQL = @strSQL + N' select top '+str(@topnum)+ ' @i = @i + 1 '
  select @strSQL = @strSQL + N',  @IdStr = '
  select @strSQL = @strSQL + N'case when @i > '+str(@previous)+' then  @IdStr + ltrim(rtrim(str(BoxNo))) +  '','' '
  select @strSQL = @strSQL + N'else N''''end '
  select @strSQL = @strSQL + N'from #wtemp '
  if(@isCheck = 1)
   begin
    --Print 'AAAAAAAAAAA'
    select @strSQL = ltrim(rtrim(@strSQL)) + N'  Where ((gw- nw >10) Or nw > gw Or gw >100 Or ( gw <> 0 And gw = nw )) '
    select @ResultSub = count(BoxNo) From #wtemp Where ((gw- nw >10) Or nw > gw Or gw >100 Or ( gw <> 0 And gw = nw ))
   end
  else
   begin
    if(@isFilter != 0)
     begin
      select @strSQL = ltrim(rtrim(@strSQL)) + N'  order by BoxNo asc '
      select @ResultSub = count(BoxNo) From #wtemp
     end
    else
     begin
      select @strSQL = ltrim(rtrim(@strSQL)) + N'  Where  (nw = 0 Or gw = 0)  order by BoxNo asc '
      select @ResultSub = count(BoxNo) From #wtemp Where  (nw = 0 Or gw = 0)
     end
   end
  Select @IdStr = N''
  --print @strSQL
  exec sp_executesql @strSQL,N'@i int,@IdStr varchar(4000) output',@i,@IdStr output
  if len(rtrim(ltrim(@IdStr))) > 0
  begin
   select @IdStr = left(@IdStr,len(@IdStr)-1)
  end
  --print @IdStr

  if(@IDStr != '')
   select @strSQL = 'select * from #wtemp as temp where BoxNo in ('+@IDStr+') Order by BoxNo '  + @SortOrder
  else
   select @strSQL = 'select * from #wtemp as temp Order by BoxNo '  + @SortOrder  --no any recorder
  --print @strSQL

  select @ResultTotal = count(BoxNo) from #wtemp
  --print @ResultTotal
  --print @ResultSub
  exec(@strSQL)
  --select top 2 * from #wtemp as temp where BoxNo in ('010001','010004') order by BoxNo   desc
  --select * from #wtemp as temp1
  --SELECT distinct  ContainerNo,BoxNo,so,PackageNo,nw,gw,Contents   FROM ssOtherItemNoBox as temp Where ContainerNo = @ContainerNo and (nw = 0 Or gw = 0) order by BoxNo
End