Advanced Search

Return to Snippet

Revision: 87
at June 30, 2006 00:44 by planetthoughtful
Initial Code 
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

    CREATE  PROCEDURE sp_Easypaging 

	(
	
	@Tables varchar(1000),
	@PK varchar(100),
	@JoinStatements varchar(1000)='',
	@Fields varchar(5000) = '*',
	@Filter varchar(5000) = NULL,
	@Sort varchar(200) = NULL,
	@PageNumber int = 1,
	@PageSize int = 10,
	@TotalRec int =0 Output,
	@Group varchar(1000) = NULL
	
		
	)

AS



/*
Created by Kashif Akram
Email [email protected]

The publication rights are reserved  
You can use this procedure with out removing these comments 
*/



DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)


SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))



--set @PK =' tbl_Items.ItemID '

create table #PageTable (PID  bigint primary key IDENTITY (1, 1) , UID int)
create table #PageIndex (UID int)

/*
CREATE UNIQUE CLUSTERED
  INDEX [PK_tbl_PageTable] ON #PageTable (PID)
*/
CREATE  
  INDEX [PK_tbl_PageIndex] ON #PageIndex (UID)


--'SELECT ' + @Fields + ' FROM ' + @Tables + '' + @JoinStatements +' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort +  ' DESC '
exec ('

set rowcount 0

	insert into #pageTable(UID) 
	 SELECT ' + @PK + ' FROM ' + @Tables + ' ' + @JoinStatements +' WHERE ' +  @Filter + ' ' + @Group + ' ORDER BY ' + @Sort +  ' 


DECLARE @SortColumn int

SET ROWCOUNT '+  @strStartRow +'

select  @SortColumn=PID from #PageTable --option (keep plan)

print @SortColumn

SET ROWCOUNT '+  @strPageSize +'

insert into #pageIndex
select UID from #PageTable where PID >= @SortColumn -- option (keep plan)


          
SELECT ' + @Fields + ' FROM ' + @Tables + ' ' + @JoinStatements +' WHERE ' +  @Filter + ' and  '+ @PK + ' in (Select UID from #pageIndex)' + @Group + ' ORDER BY ' + @Sort +  ' '

 )



select @TotalRec=count(*) from  #pageTable 





drop table #PageTable 
drop table #PageIndex


	
RETURN 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
Example usage:

exec sp_easypaging '[tblmessages]','[tblmessages].recid','','*','1 = 1','recid',1,5

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
SQLServer 2000 T-SQL Stored Procedure for providing paginated results

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
sql, page

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL