|
|
|
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
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 ) |
|
|