Posted By

planetthoughtful on 06/30/06

Tagged

sql
page

Versions (?)

Last Edited at 06/30/06 12:44am

Statistics

Viewed 1319 times

Favorited by 0 user(s)

Related snippets

SQLServer 2000 T-SQL Stored Procedure for providing paginated results

/ Published in: SQL

Example usage:

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

Highlighted TextExpand

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
	
		
	)

/*
Created by Kashif Akram
Email Muhammad_kashif@msn.com

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

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.