GO
/****** Object:  StoredProcedure [dbo].[T_GetPager]    Script Date: 03/23/2015 14:35:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[T_GetPager]    
@PageSize INT,    
@PageIndex INT,    
@orderby varchar(150),  
@strWhere varchar(4000),  
@tablename varchar(64),  
@cols varchar(4000)  ,
@groupby varchar(256)
   
AS     
Begin  
 declare @strSQL varchar(5000)  
 declare @b bigint,@e bigint  
 declare @order varchar(160)  
   
 IF(@orderby<>'')  
  SET @order = ' ORDER BY '+@orderby  
 ELSE  
  SET @order = ' ORDER BY  id DESC'  
  
 SET @b=@PageSize*(@PageIndex-1)+1  
 SET @e=@PageSize*@PageIndex  
  
 SET @strSQL = 'select  ' + @cols  
 SET @strSQL = @strSQL + ' from '  
 SET @strSQL = @strSQL+ '(select row_number() over (' + @order + ') row ,' + @cols + ' from ' + @tablename  
 IF (@strWhere<>'')  
  SET @strSQL= @strSQL + ' WHERE ' + @strWhere   
   IF(@groupby<>'')
  SET @strSQL=@strSQL + ' group by ' + @groupby 
 SET @strSQL = @strSQL + ') '  
 SET @strSQL = @strSQL + @tablename   
 if(@PageSize>0)
  begin
    SET @strSQL = @strSQL + ' where row between ' + CAST(@b AS varchar)  
    SET @strSQL = @strSQL + ' and ' + CAST(@e AS varchar)  
  end
   
   
 IF(@groupby<>'')
  SET @strSQL=@strSQL + ' group by ' + @groupby 

 
 SET @strSQL = @strSQL +' ' + @order  
End  
  print @strSQL
exec(@strSQL)