SQL資料分頁

yyldir發表於2008-09-26
SQL
-- 查詢滿足條件的記錄集,並獲取指定頁的資料
CREATE                  PROCEDURE Proc_QueryPages

@Fieldstr NVARCHAR(255) =' * ', -- 查詢欄位
@Tnamestr NVARCHAR(255) ='', -- 查詢表名
@Wherestr NVARCHAR(255) ='', -- 查詢條件
@Orderby  Nvarchar(255) =' order by id desc ', -- 查詢的排序
@PageSize int = 50,          -- 頁尺寸
@PageIndex int =1,           -- 頁碼
@mode int =1,                --返回模式1為資料集,0為資料總數和總頁數
@Pagemode int =1,         --是否分頁,1為分頁,0表示不分頁
@CountRs int = 1 output,         ----查詢到的記錄數
@Countpages int = 1 output             ----查詢結果總頁數

AS
declare @SQLstr Nvarchar(4000)
declare @Wheretemp1 Nvarchar(1000)
declare @Orderfield1 Nvarchar(1000)
declare @OrderTmp1 Nvarchar(1000)
declare @Orderby1 Nvarchar(1000)

if @mode != 0    --判斷結果是統計記錄數還是返回資料集,非零返回資料集
BEGIN
  if @Pagemode=1 --"1"為分頁
    begin
    if @PageIndex=1
        if @Wherestr=''
            set @SQLstr='select top '+ltrim(str(@PageIndex*@PageSize)) +' '+ @Fieldstr + ' from '+ @Tnamestr +' '+@Orderby
        else
                  set @SQLstr='select top '+ltrim(str(@PageIndex*@PageSize)) +' '+ @Fieldstr + ' from '+ @Tnamestr +' where '+ @Wherestr+' '+@Orderby
        --以上程式碼是查詢第一頁的,這樣可以加速查詢
    else
        IF charindex(',',@Orderby)>0
            Begin
                set @SQLstr='SELECT TOP '+ltrim(str(@PageSize))+' '+@Fieldstr
                    +' FROM ' + @Tnamestr + ' WHERE (ID NOT IN (SELECT id FROM (SELECT top '+ltrim(str((@PageIndex-1)*@PageSize))
                    +' * FROM ' + @Tnamestr +@Orderby+') AS t)) '+@Orderby
            End
        ELSE
            Begin
                set @Orderfield1=replace(replace(replace(@Orderby,'order by',''),'desc',''),'asc','')
                ----設定排序的條件字串
                if charindex('desc', @Orderby)>0
                    set @Wheretemp1=' where '+@Orderfield1+'<(select min('+@Orderfield1+') from (select top '
                else
                    set @Wheretemp1=' where '+@Orderfield1+'>(select max('+@Orderfield1+') from (select top '
                if @Wherestr=''
                    set @SQLstr='select top '+ltrim(str(@PageSize))+' '+@Fieldstr
                    +' from '+@Tnamestr+' '+@wheretemp1
                    +ltrim(str((@PageIndex-1)*@PageSize))
                    +' '+@Orderfield1+' from '+@Tnamestr+' '+@Orderby+') as T ) '+@Orderby
                ELSE
                    set @SQLstr='select top '+ltrim(str(@PageSize))+' '+@Fieldstr
                    +' from '+@Tnamestr+' '+@wheretemp1
                    +ltrim(str((@PageIndex-1)*@PageSize))
                    +' '+@Orderfield1+' from '+@Tnamestr+' where '+@Wherestr+' '+@Orderby+') as T ) AND '
                    +@Wherestr+' '+@Orderby
            End
          end
  else  --不分頁模式程式碼開始
      begin
    if @Wherestr=''
        set @SQLstr='select ' + @Fieldstr + ' from '+ @Tnamestr +' '+@Orderby
    else
          set @SQLstr='select ' + @Fieldstr + ' from '+ @Tnamestr +' where '+ @Wherestr+' '+@Orderby
      end

  exec(@SQLstr)-- 執行查詢
 --print @SQLstr

  END
else    --返回記錄數和總頁數程式碼開始
  BEGIN
    if @Wherestr=''
        set @SQLstr = 'select @CountRs=count(*) from ' + @Tnamestr
    Else
        set @SQLstr = 'select @CountRs=count(*) from ' + @Tnamestr +' where '+ @Wherestr
        exec sp_executesql @SQLstr,N'@CountRs int out ',@CountRs out

    if @CountRs<=@PageSize
        set @Countpages=1
    else
        begin
            set @Countpages=cast(@CountRs/@PageSize as int)
            if (@CountPages * @PageSize != @CountRs)
            set @Countpages=@CountRs/@PageSize+1
        end
    --print @CountRs
    --print @Countpages
   END





GO

相關文章