advanced code snippet search

We Recommend

SQL Cookbook SQL Cookbook
Written in O'Reilly's popular Problem/Solution/Discussion style, the SQL Cookbook is sure to please. Anthony's credo is: "When it comes down to it, we all go to work, we all have bills to pay, and we all want to go home at a reasonable time and enjoy what's still available of our days." The SQL Cookbook moves quickly from problem to solution, saving you time each step of the way.

Posted By

planetthoughtful on 06/30/06


Tagged

sql page paginate mssql 2k sproc stored procedure


Versions (?)


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

Plain TextExpand 
  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 Muhammad_kashif@msn.com
  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 

You need to login to post a comment.