SQLServer 2000 T-SQL Stored Procedure for providing paginated results


/ Published in: SQL
Save to your folder(s)

Example usage:

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


Copy this code and paste it in your HTML
  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

You need to login to post a comment.