MSSQL通用分页存储过程

 

 
   
  set 
   ANSI_NULLS  
  ON 
  
 
  set 
   QUOTED_IDENTIFIER  
  ON 
  
 
  go 
  


 
  ALTER 
           
  PROCEDURE 
    
  [ 
  dbo 
  ] 
  . 
  [ 
  GetDataSet 
  ] 
   
 
  @TableList 
    
  Varchar 
  ( 
  200 
  ) 
  = 
  ' 
  * 
  ' 
  ,
 
  @TableName 
    
  Varchar 
  ( 
  300 
  ),
 
  @SelectWhere 
    
  Varchar 
  ( 
  1000 
  ) 
  = 
  '' 
  ,
 
  @SelectOrderId 
    
  Varchar 
  ( 
  200 
  ),
 
  @SelectOrder 
    
  Varchar 
  ( 
  200 
  ) 
  = 
  '' 
  ,
 
  @intPageNo 
    
  int 
  = 
  1 
  ,  
  -- 
  页号 
  
 
  @intPageSize 
    
  int 
  = 
  10 
    
  -- 
  每页显示数 
  
 
  as 
  
 
  begin 
  
     
  set 
   nocount  
  on 
  -- 
  关闭计数 
  
 
       
  declare 
    
  @RecordCount 
    
  int 
     
  -- 
  总记录数(存储过程输出参数) 
  
 
       
  declare 
    
  @TmpSelect 
         
  NVarchar 
  ( 
  2000 
  )  
     
  declare 
    
  @Tmp 
        
  NVarchar 
  ( 
  600 
  )  

     
  if 
    
  @SelectWhere 
  = 
  '' 
  
         
  begin 
  
             
  set 
    
  @TmpSelect 
    
  = 
    
  ' 
  select @RecordCount = count(*) from  
  ' 
  + 
  @TableName 
  
         
  end 
  
     
  else 
  
         
  begin 
  
             
  set 
    
  @TmpSelect 
    
  = 
    
  ' 
  select @RecordCount = count(*) from  
  ' 
  + 
  @TableName 
    
  + 
  ' 
   where  
  ' 
  + 
  @SelectWhere 
  
         
  end 
  
     
  execute 
   sp_executesql   
  @TmpSelect 
  ,     
  -- 
  执行上面的sql语句 
  
 
      N 
  ' 
  @RecordCount int OUTPUT 
  ' 
   ,   
  -- 
  执行输出数据的sql语句，output出总记录数 
  
 
       
  @RecordCount 
    OUTPUT
     
  -- 
  if (@RecordCount = 0)    --如果没有，则返回零 
  
 
                  
  -- 
  return 0 
  
 
       
  /* 
  判断页数是否正确 
  */ 
  
       
  if 
   ( 
  @intPageNo 
    
  - 
    
  1 
  )  
  * 
    
  @intPageSize 
    
  > 
    
  @RecordCount 
      
  -- 
  页号大于总页数，返回错误 
  
 
           
  return 
   ( 
  - 
  1 
  )
     
  -- 
  set nocount off--打开计数 
  
 
  
     
  if 
    
  @SelectWhere 
    
  != 
    
  '' 
   
         
  begin 
  
             
  set 
    
  @TmpSelect 
    
  = 
    
  ' 
  select top  
  ' 
  + 
  str 
  ( 
  @intPageSize 
  ) 
  + 
  ' 
    
  ' 
  + 
  @TableList 
  + 
  ' 
   ,  
  ''' 
  + 
  str 
  ( 
  @RecordCount 
  ) 
  + 
  ''' 
   as Count from  
  ' 
  + 
  @TableName 
  + 
  ' 
   where  
  ' 
  + 
  @SelectOrderId 
  + 
  ' 
   not in(select top  
  ' 
  + 
  str 
  (( 
  @intPageNo 
  - 
  1 
  ) 
  * 
  @intPageSize 
  ) 
  + 
  ' 
    
  ' 
  + 
  @SelectOrderId 
  + 
  ' 
   from  
  ' 
  + 
  @TableName 
  + 
  ' 
   where 1=1 and  
  ' 
  + 
  @SelectWhere 
    
  + 
  ' 
    
  ' 
  + 
  @SelectOrder 
  + 
  ' 
  ) and  
  ' 
  + 
  @SelectWhere 
    
  + 
  ' 
    
  ' 
  + 
  @SelectOrder 
  
         
  end 
  
     
  else 
  
         
  begin 
  
             
  set 
    
  @TmpSelect 
    
  = 
    
  ' 
  select top  
  ' 
  + 
  str 
  ( 
  @intPageSize 
  ) 
  + 
  ' 
    
  ' 
  + 
  @TableList 
  + 
  ' 
   ,  
  ''' 
  + 
  str 
  ( 
  @RecordCount 
  ) 
  + 
  ''' 
   as Count from  
  ' 
  + 
  @TableName 
  + 
  ' 
   where  
  ' 
  + 
  @SelectOrderId 
  + 
  ' 
   not in(select top  
  ' 
  + 
  str 
  (( 
  @intPageNo 
  - 
  1 
  ) 
  * 
  @intPageSize 
  ) 
  + 
  ' 
    
  ' 
  + 
  @SelectOrderId 
  + 
  ' 
   from  
  ' 
  + 
  @TableName 
  + 
  ' 
    
  ' 
  + 
  @SelectOrder 
  + 
  ' 
  )  
  ' 
  + 
  @SelectOrder 
  
         
  end 
  
     
  execute 
   sp_executesql  
  @TmpSelect 
  
     
  return 
  ( 
  @@rowcount 
  )
 
  end