Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Procedure to return a page of records

Author  Topic 

iminore
Posting Yak Master

141 Posts

Posted - 2006-11-09 : 10:37:07
This is a procedure to return a requested page from a passed table or view ordered by passed columns and with a passed position.

CREATE PROCEDURE [dbo].[PageGet]
@table varchar(80),
@col1 varchar(80) = null,
@col2 varchar(80) = null,
@col3 varchar(80) = null,
@col4 varchar(80) = null,
@colorder1 varchar(80),
@colorder2 varchar(80) = null,
@position varchar(80) = null,
@pagesize int,
@pageno int
as
-- return a recordset of a passed page no. of a passed table/view for passed columns
set nocount on

declare @pages int, @str varchar(800), @strsub varchar(80)

set @pages = @pagesize * @pageno
set @strsub = ' ' + @col1
if @col2 is not null and @col2 <> ''
set @strsub = @strsub + ', ' + @col2
if @col3 is not null and @col3 <> ''
set @strsub = @strsub + ', ' + @col3
if @col4 is not null and @col4 <> ''
set @strsub = @strsub + ', ' + @col4
set @str = 'select ' + @strsub + ' from (select top ' + cast(@pagesize as varchar) + @strsub + ' from (select top ' + cast(@pages as varchar) + @strsub + ' from ' + @table
if @position is not null and @position <> ''
set @str = @str + ' where ' + @colorder1 + ' >= ''' + @position + ''' '
set @str = @str + ' order by ' + @colorder1
if @colorder2 is not null and @colorder2 <> ''
set @str = @str + ', ' + @colorder2
set @str = @str + ') a order by ' + @colorder1 + ' desc '
if @colorder2 is not null and @colorder2 <> ''
set @str = @str + ', ' + @colorder2 + ' desc '
set @str = @str + ') b order by ' + @colorder1
if @colorder2 is not null and @colorder2 <> ''
set @str = @str + ', ' + @colorder2
execute(@str)

GO
   

- Advertisement -