Atlanta Custom Software Development 

 
   Search        Code/Page
 

User Login
Email

Password

 

Forgot the Password?
Services
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
Programming
  Database
Automation
OS/Networking
Graphics
Links
Tools
» Regular Expr Tester
» Free Tools

Serverside Paging using dynamic T-SQL

Total Hit ( 2086)

Rate this article:     Poor     Excellent 

 Submit Your Question/Comment about this article

Rating


 


This stored Procedure can page records for any specified query.

Click here to copy the following block
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*

Description: Page a query. Query can have order by clause or it can pass separate orderby parameter

Examples:
=========================

[1] Default sort and default poagenumber/size
--------------------------------------------------------
exec sp_PageQuery 'select * from northwind.dbo.orders'


[2] Page#1 and Pagesize 30
--------------------------------------------------------
declare @a int
exec sp_PageQuery 'select * from northwind.dbo.orders order by customerid',NULL,1,30,@a out
Print 'Total Rows' + str(@a)

[3] Page#3 and Pagesize 30, order by orderid, customerid desc
--------------------------------------------------------
declare @b int
exec sp_PageQuery 'select * from northwind.dbo.orders','orderid,customerid desc',3,30,@b out
Print 'Total Rows' + str(@b)

*/

ALTER PROCEDURE sp_PageQuery
(
@Select varchar(8000),
@OrderBy varchar(2000)='',
@PageNum int=1,
@PageSize int=30,
@TotalRows int=0 OUTPUT
)

AS
BEGIN

declare @ColList varchar(8000);
declare @Where varchar(8000);
declare @i int;
declare @i2 int;
declare @tmp varchar(8000);
declare @dec varchar(8000);
declare @f varchar(100);
declare @d varchar(100);
declare @Symbol char(2);
declare @sTmp varchar(2000)
declare @SQL varchar(8000);
declare @Sort varchar(2000);

declare @StartRow int;
declare @EndRow int;

set @StartRow = ((@PageNum-1)* @PageSize)+1
set @EndRow = @StartRow + @PageSize - 1

set @OrderBy=isnull(@OrderBy,'')
if ltrim(rtrim(@OrderBy))=''
   set @OrderBy='1'

if @OrderBy='1'
begin
set @i = charindex('order by',@Select)
if @i>0
begin
   set @OrderBy = ltrim(rtrim(right(@Select,len(@Select)-@i-8)))
    set @Select = left(@Select,@i-1)
end
end

create table #recCount(RecCount int)
exec('insert into #recCount (RecCount) select count(*) from ('+@Select+') a')
select @TotalRows=RecCount from #recCount
drop table #recCount

set @Sort = @OrderBy + ', '
set @dec = ''
set @Where = ''
set @SQL = ''

set @i = charindex(',' , @Sort)
while @i != 0
begin
set @tmp = left(@Sort,@i-1)
set @i2 = charindex(' ', @tmp)

set @f = case when @i2=0 then ltrim(rtrim(@tmp)) else ltrim(rtrim(left(@tmp,@i2-1))) end
set @d = case when @i2=0 then '' else ltrim(rtrim(substring(@tmp,@i2+1,100))) end

set @Sort = rtrim(ltrim(substring(@Sort,@i+1,100)))
set @i = charindex(',', @Sort)
set @symbol = case when @d = 'DESC' then '<' else '>' end +
    case when @i=0 then '=' else '' end

set @dec = @dec + 'declare @' + @f + ' sql_variant; '
set @ColList = isnull(replace(replace(@colList,'>','='),'<','=') + ' and ','') +
     @f + ' ' + @Symbol + ' @' + @f
set @Where = @Where + ' OR (' + @ColList + ') '
set @SQL = @SQL + ', @' + @f + '= ' + @f
end

set @SQL = @dec + ' ' +
   'SET ROWCOUNT ' + convert(varchar(10), @StartRow) + '; ' +
   'SELECT ' + substring(@SQL,3,7500) + ' from (' + @Select + ') a ORDER BY ' +
   @OrderBy + '; ' + 'SET ROWCOUNT ' +
   convert(varchar(10), 1 + @EndRow - @StartRow) + '; ' +
   'select * from (' + @Select + ') a WHERE ' +
   substring(@Where,4,7500) + ' ORDER BY ' + @OrderBy + '; SET ROWCOUNT 0;'

exec(@SQL)
PRINT @SQL
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)

Job Description : He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )


Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  BlogsZappySys

© 2008 BinaryWorld LLC. All rights reserved.