sql儲存過程分頁

wl1121發表於2009-09-04
SQL儲存過程

ALTER  procedure Consignment  
@tablename varchar(80) ,  
@strOrder varchar(50) ,  
@PageIndex int = 1,  
@PageSize int = 15,  
@strGetFields varchar(200) = '*',  
@OutPut int output  
as   
Begin  
Declare @strSql varchar(500)  
  
DECLARE   @SQL   NVARCHAR(1000)  
DECLARE   @R BIGINT  
SET   @SQL=  N'select @R=count(*) from  '+@TableName  
EXEC  SP_EXECUTESQL   @SQL,  N' @R BIGINT OUTPUT',  @R OUTPUT  
SET   @OutPut=  @R  
  
if(@PageIndex =1)  
Begin  
   set @strSql='select top '+str(@PageSize)+' '+@strGetFields+' from '+@tablename+' order by '+@strOrder  
End  
Else  
   set @strSql='select top '+str(@PageSize)+' '+@strGetFields+' from '+@tablename+' where ('+@strOrder  
             +' >= ( select Max('+@strOrder+') from ( select top '+str(@PageSize*@PageIndex)+' * from '+@tablename+' order by '  
             +@strOrder+' ) as tempTable)) order by '+ @strOrder  
   select @strSql    
exec(@strSql)  
  
End
 alter procedure AllProce 
@tablename varchar(200) ,   --表名  
@strGetFields varchar(200) = '*',  --查詢列名  
@PageIndex int = 1 ,         --頁碼  
@pageSize int = 15,         --頁面大小  
@strWhere  varchar(100) = '',     --查詢條件  
@strOrder varchar(100) = '', --排序列名  
@intOrder bit = 0,        --排序型別  1為升序  
@CountAll bigint output              --返回紀錄總數用於計算頁面數      
as  
begin  
declare @strSql varchar(500)  --主語句  
declare @strTemp varchar(100) --臨時變數  
declare @strOrders varchar(50) --排序語句  
declare @table varchar(70)  
  
declare   @SQL   nvarchar(1000)  
declare   @R bigint  
set   @SQL=  N'select @R=count(*) from  '+convert(nvarchar(200),@TableName)  
exec  SP_EXECUTESQL   @SQL,  N' @R BIGINT OUTPUT',  @R OUTPUT  
set   @CountAll=  @R  
if @intOrder = 0  
begin  
    --為0是升序  
    set @strTemp = '>(select max'  
    set @strOrders =  ' order by  '+@strOrder+' asc '  
end  
else  
begin  
    --否則為降序  
    set @strTemp = '<(select min'  
    set @strOrders = ' order by  '+@strOrder+' desc '  
end  
if @PageIndex =1        --第一頁直接讀出紀錄  
begin  
    if @strWhere = ''  
    begin  
         set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' '+@strOrders  
     end  
    else    
    begin  
         set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+ ' from '+@tablename+' where '+@strWhere+' '+@strOrders  
      
    end  
end  
else  
begin  
    set @strSql = 'select top'+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strOrder+' '+@strTemp+' ('+@strOrder+')'  
                  +' from (select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+ ' '+@strOrders+ ') as tempTable ) '+@strOrders  
           
    if @strWhere != ' '  
    begin  
       set @strSql = 'select top '+str(@pageSize)+ ' '+@strGetFields+' from '+@tablename+ ' where '+@strOrder+ ' '+@strTemp+' ('+@strOrder+') '  
                   +' from(select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strWhere+' ' +@strOrders+') as tempTable) where '+@strWhere+' '+@strOrders  
     
    end  
end   
exec(@strSql)      
end  
GO
 

相關文章