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.
| 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 intas-- return a recordset of a passed page no. of a passed table/view for passed columnsset nocount ondeclare @pages int, @str varchar(800), @strsub varchar(80)set @pages = @pagesize * @pagenoset @strsub = ' ' + @col1if @col2 is not null and @col2 <> '' set @strsub = @strsub + ', ' + @col2if @col3 is not null and @col3 <> '' set @strsub = @strsub + ', ' + @col3if @col4 is not null and @col4 <> '' set @strsub = @strsub + ', ' + @col4set @str = 'select ' + @strsub + ' from (select top ' + cast(@pagesize as varchar) + @strsub + ' from (select top ' + cast(@pages as varchar) + @strsub + ' from ' + @tableif @position is not null and @position <> '' set @str = @str + ' where ' + @colorder1 + ' >= ''' + @position + ''' 'set @str = @str + ' order by ' + @colorder1if @colorder2 is not null and @colorder2 <> '' set @str = @str + ', ' + @colorder2set @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 ' + @colorder1if @colorder2 is not null and @colorder2 <> '' set @str = @str + ', ' + @colorder2execute(@str)GO |
|
|
|
|
|
|
|
Procedure to return a page of records