SQL資料分頁

yyldir發表於2008-09-26
-- 查詢滿足條件的記錄集,並獲取指定頁的資料
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

相關文章