Posted By

planetthoughtful on 06/30/06


Tagged

sql page paginate mssql 2k sproc stored procedure


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

aspcorpo


SQLServer 2000 T-SQL Stored Procedure for providing paginated results


 / Published in: SQL
 

Example usage:

exec sp_easypaging '[tblmessages]','[tblmessages].recid','','*','1 = 1','recid',1,5

  1. SET QUOTED_IDENTIFIER ON
  2. GO
  3. SET ANSI_NULLS ON
  4. GO
  5.  
  6. CREATE PROCEDURE sp_Easypaging
  7.  
  8. (
  9.  
  10. @TABLES varchar(1000),
  11. @PK varchar(100),
  12. @JoinStatements varchar(1000)='',
  13. @FIELDS varchar(5000) = '*',
  14. @Filter varchar(5000) = NULL,
  15. @Sort varchar(200) = NULL,
  16. @PageNumber int = 1,
  17. @PageSize int = 10,
  18. @TotalRec int =0 Output,
  19. @GROUP varchar(1000) = NULL
  20.  
  21.  
  22. )
  23.  
  24. AS
  25.  
  26.  
  27.  
  28. /*
  29. Created by Kashif Akram
  30.  
  31. The publication rights are reserved
  32. You can use this procedure with out removing these comments
  33. */
  34.  
  35.  
  36.  
  37. DECLARE @strPageSize varchar(50)
  38. DECLARE @strStartRow varchar(50)
  39.  
  40.  
  41. SET @strPageSize = CAST(@PageSize AS varchar(50))
  42. SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))
  43.  
  44.  
  45.  
  46. --set @PK =' tbl_Items.ItemID '
  47.  
  48. CREATE TABLE #PageTable (PID bigint primary key IDENTITY (1, 1) , UID int)
  49. CREATE TABLE #PageIndex (UID int)
  50.  
  51. /*
  52. CREATE UNIQUE CLUSTERED
  53.   INDEX [PK_tbl_PageTable] ON #PageTable (PID)
  54. */
  55. CREATE
  56. INDEX [PK_tbl_PageIndex] ON #PageIndex (UID)
  57.  
  58.  
  59. --'SELECT ' + @Fields + ' FROM ' + @Tables + '' + @JoinStatements +' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' DESC '
  60. exec ('
  61.  
  62. set rowcount 0
  63.  
  64. insert into #pageTable(UID)
  65. SELECT ' + @PK + ' FROM ' + @TABLES + ' ' + @JoinStatements +' WHERE ' + @Filter + ' ' + @GROUP + ' ORDER BY ' + @Sort + '
  66.  
  67.  
  68. DECLARE @SortColumn int
  69.  
  70. SET ROWCOUNT '+ @strStartRow +'
  71.  
  72. select @SortColumn=PID from #PageTable --option (keep plan)
  73.  
  74. print @SortColumn
  75.  
  76. SET ROWCOUNT '+ @strPageSize +'
  77.  
  78. insert into #pageIndex
  79. select UID from #PageTable where PID >= @SortColumn -- option (keep plan)
  80.  
  81.  
  82.  
  83. SELECT ' + @FIELDS + ' FROM ' + @TABLES + ' ' + @JoinStatements +' WHERE ' + @Filter + ' and '+ @PK + ' in (Select UID from #pageIndex)' + @GROUP + ' ORDER BY ' + @Sort + ' '
  84.  
  85. )
  86.  
  87.  
  88.  
  89. SELECT @TotalRec=count(*) FROM #pageTable
  90.  
  91.  
  92.  
  93.  
  94.  
  95. DROP TABLE #PageTable
  96. DROP TABLE #PageIndex
  97.  
  98.  
  99.  
  100. RETURN
  101.  
  102. GO
  103. SET QUOTED_IDENTIFIER OFF
  104. GO
  105. SET ANSI_NULLS ON
  106. GO

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: aspcorpo on June 25, 2010

could you please tell me whats eg: of use?

USE [Database] GO

DECLARE @return_value int, @TotalRec int

SELECT @TotalRec = *

EXEC @returnvalue = [dbo].[spEasypaging] @TABLES = N'', @PK = N'', @JoinStatements = N'', @FIELDS = N'', @Filter = N'', @Sort = N'', @PageNumber = *, @PageSize = *, @TotalRec = @TotalRec OUTPUT, @GROUP = N'*'

SELECT @TotalRec as N'@TotalRec'

SELECT 'Return Value' = @return_value

GO

You need to login to post a comment.