Advanced Search

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

Expand | Embed | Plain Text

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. Email [email protected]
  31.  
  32. The publication rights are reserved  
  33. You can use this procedure with out removing these comments 
  34. */
  35.  
  36.  
  37.  
  38. DECLARE @strPageSize VARCHAR(50)
  39. DECLARE @strStartRow VARCHAR(50)
  40.  
  41.  
  42. SET @strPageSize = CAST(@PageSize AS VARCHAR(50))
  43. SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS VARCHAR(50))
  44.  
  45.  
  46.  
  47. --set @PK =' tbl_Items.ItemID '
  48.  
  49. CREATE TABLE #PageTable (PID  BIGINT PRIMARY KEY IDENTITY (1, 1) , UID INT)
  50. CREATE TABLE #PageIndex (UID INT)
  51.  
  52. /*
  53. CREATE UNIQUE CLUSTERED
  54.   INDEX [PK_tbl_PageTable] ON #PageTable (PID)
  55. */
  56. CREATE  
  57.   INDEX [PK_tbl_PageIndex] ON #PageIndex (UID)
  58.  
  59.  
  60. --'SELECT ' + @Fields + ' FROM ' + @Tables + '' + @JoinStatements +' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort +  ' DESC '
  61. EXEC ('
  62.  
  63. set rowcount 0
  64.  
  65. 	insert into #pageTable(UID) 
  66. 	 SELECT ' + @PK + ' FROM ' + @TABLES + ' ' + @JoinStatements +' WHERE ' +  @FILTER + ' ' + @GROUP + ' ORDER BY ' + @Sort +  ' 
  67.  
  68.  
  69. DECLARE @SortColumn int
  70.  
  71. SET ROWCOUNT '+  @strStartRow +'
  72.  
  73. select  @SortColumn=PID from #PageTable --option (keep plan)
  74.  
  75. print @SortColumn
  76.  
  77. SET ROWCOUNT '+  @strPageSize +'
  78.  
  79. insert into #pageIndex
  80. select UID from #PageTable where PID >= @SortColumn -- option (keep plan)
  81.  
  82.  
  83.  
  84. SELECT ' + @FIELDS + ' FROM ' + @TABLES + ' ' + @JoinStatements +' WHERE ' +  @FILTER + ' and  '+ @PK + ' in (Select UID from #pageIndex)' + @GROUP + ' ORDER BY ' + @Sort +  ' '
  85.  
  86.  )
  87.  
  88.  
  89.  
  90. SELECT @TotalRec=COUNT(*) FROM  #pageTable 
  91.  
  92.  
  93.  
  94.  
  95.  
  96. DROP TABLE #PageTable 
  97. DROP TABLE #PageIndex
  98.  
  99.  
  100.  
  101. RETURN 
  102.  
  103. GO
  104. SET QUOTED_IDENTIFIER OFF 
  105. GO
  106. SET ANSI_NULLS ON 
  107. GO

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.