|
iminore
Posting Yak Master
United Kingdom
141 Posts |
 Posted - 11/09/2006 : 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
|
|
Procedure to return a page of records
