SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Procedure to return a page of records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

iminore
Posting Yak Master

United Kingdom
141 Posts

Posted - 11/09/2006 :  10:37:07  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000