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. 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
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.