ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[Paging_Cursor]') andOBJECTPROPERTY(id, N'IsProcedure') =1) dropprocedure[dbo].[Paging_Cursor] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATEPROCEDURE Paging_Cursor ( @Tablesvarchar(1000), @PKvarchar(100), @Sortvarchar(200) =NULL, @PageNumberint=1, @PageSizeint=10, @Fieldsvarchar(1000) ='*', @Filtervarchar(1000) =NULL, @Groupvarchar(1000) =NULL) AS /**//*Find the @PK type*/ DECLARE@PKTablevarchar(100) DECLARE@PKNamevarchar(100) DECLARE@typevarchar(100) DECLARE@precint IFCHARINDEX('.', @PK) >0 BEGIN SET@PKTable=SUBSTRING(@PK, 0, CHARINDEX('.',@PK)) SET@PKName=SUBSTRING(@PK, CHARINDEX('.',@PK) +1, LEN(@PK)) END ELSE BEGIN SET@PKTable=@Tables SET@PKName=@PK END SELECT@type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype WHERE o.name =@PKTableAND c.name =@PKName IFCHARINDEX('char', @type) >0 SET@type=@type+'('+CAST(@precASvarchar) +')' DECLARE@strPageSizevarchar(50) DECLARE@strStartRowvarchar(50) DECLARE@strFiltervarchar(1000) DECLARE@strGroupvarchar(1000) /**//*Default Sorting*/ IF@SortISNULLOR@Sort='' SET@Sort=@PK /**//*Default Page Number*/ IF@PageNumber<1 SET@PageNumber=1 /**//*Set paging variables.*/ SET@strPageSize=CAST(@PageSizeASvarchar(50)) SET@strStartRow=CAST(((@PageNumber-1)*@PageSize+1) ASvarchar(50)) /**//*Set filter & group variables.*/ IF@FilterISNOTNULLAND@Filter!='' SET@strFilter=' WHERE '+@Filter+'' ELSE SET@strFilter='' IF@GroupISNOTNULLAND@Group!='' SET@strGroup=' GROUP BY '+@Group+'' ELSE SET@strGroup='' /**//*Execute dynamic query*/ EXEC( 'DECLARE @PageSize int SET @PageSize = '+@strPageSize+' DECLARE @PK '+@type+' DECLARE @tblPK TABLE ( PK '+@type+' NOT NULL PRIMARY KEY ) DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR SELECT '+@PK+' FROM '+@Tables+@strFilter+''+@strGroup+' ORDER BY '+@Sort+' OPEN PagingCursor FETCH RELATIVE '+@strStartRow+' FROM PagingCursor INTO @PK SET NOCOUNT ON WHILE @PageSize > 0 AND @@FETCH_STATUS = 0 BEGIN INSERT @tblPK (PK) VALUES (@PK) FETCH NEXT FROM PagingCursor INTO @PK SET @PageSize = @PageSize - 1 END CLOSE PagingCursor DEALLOCATE PagingCursor SELECT '+@Fields+' FROM '+@Tables+' JOIN @tblPK tblPK ON '+@PK+' = tblPK.PK '+@strFilter+''+@strGroup+' ORDER BY '+@Sort ) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[Paging_Cursor]) and OBJECTPROPERTY(id, NIsProcedure) = 1) drop procedure [dbo].[Paging_Cursor] GO SET QUOTED_IDENTIFIER ON GO