February 28, 2006 at 4:44 pm
Coming on a deadline..actually tommorow...I need to do pagination on a web page with a stored proc/PHP. I have done it with an easy query statement in PHP but I really would like to get it done with a stored proc. I have scoured the web and forums to no avail. Any direction would be greatly appreciated.
Thanks
February 28, 2006 at 7:23 pm
March 2, 2006 at 10:36 am
Here's my solution.
CREATE PROCEDURE usp_rpt_documents_paging
(
@USERS_ID INT = NULL
,@PIPELINES_ID INT = NULL
,@PAGE INT = NULL OUTPUT
,@ROWS_PER_PAGE INT = NULL
,@DOC_TYPE VARCHAR(255) = NULL
,@SORT_CHOICE TINYINT = 0
,@SORT_DESC BIT = 0
,@TEST TINYINT = 0
)
/***********************************************************************************************************
Create by: sprocKing
Date: 20050924
Purpose: Server side document paging.
Modified by
Date:
Note:
***********************************************************************************************************/
/*_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_
EXECUTE @ERROR = USP_RPT_DOCUMENTS_PAGING
@PIPELINES_ID = @PIPELINES_ID -- @PIPELINES_ID INT
,@PAGE = @PAGE OUTPUT -- @PAGE INT = NULL OUTPUT
,@ROWS_PER_PAGE = @ROWS_PER_PAGE -- @ROWS_PER_PAGE INT = NULL
,@DOC_TYPE = @DOC_TYPE -- @DOC_TYPE VARCHAR(255) = NULL
,@SORT_CHOICE = @SORT_CHOICE -- @SORT_CHOICE TINYINT = 0
,@SORT_DESC = @SORT_DESC -- @SORT_DESC BIT = 0
_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_TEMPLATE_*/
/*_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/
AS
SET NOCOUNT ON
BEGIN
DECLARE @ERROR INT
DECLARE @NO_ERROR BIT
DECLARE @USER_DEFINED_ERROR INT
DECLARE @FAIL VARCHAR(100)
DECLARE @USAGE VARCHAR(2000)
DECLARE @INSERT_STRING VARCHAR(1000)
DECLARE @WHERE_STRING VARCHAR(200)
DECLARE @ORDER_BY_STRING VARCHAR(200)
DECLARE @SQL_STRING VARCHAR(1200)
DECLARE @tab CHAR(1)
DECLARE @CR_LF CHAR(1)
DECLARE @CR_LF_3 CHAR(3)
DECLARE @CR_LF_TAB CHAR(2)
DECLARE @CR_LF_TAB_2 CHAR(3)
DECLARE @CR_LF_TAB_3 CHAR(4)
DECLARE @CR_LF_3_TAB CHAR(4)
-- DECLARE @TBL_DOCUMENTS_PAGED TABLE (
-- ROW_ID INT IDENTITY
-- ,DOCUMENTS_ID INT
-- )
SET @tab = CHAR(9)
SET @CR_LF = CHAR(13)
SET @CR_LF_TAB = @CR_LF + @tab
SET @CR_LF_TAB_2 = @CR_LF_TAB + @tab
SET @CR_LF_TAB_3 = @CR_LF_TAB_2 + @tab
SET @CR_LF_3 = @CR_LF + @CR_LF + @CR_LF
SET @CR_LF_3_TAB = @CR_LF_3 + @tab
SET @NO_ERROR = 0
SET @USER_DEFINED_ERROR = 50001
SET @FAIL = 'Failure in USP_RPT_DOCUMENTS_PAGING: '
SET @USAGE =
'USAGE:' + @CR_LF_TAB
+ 'EXECUTE @ERROR = USP_RPT_DOCUMENTS_PAGING' + @CR_LF_TAB_2
+ ',@PIPELINES_ID = @PIPELINES_ID' + @CR_LF_TAB_2
+ '[,@PAGE = @PAGE OUTPUT]' + @CR_LF_TAB_2
+ '[,@ROWS_PER_PAGE = @ROWS_PER_PAGE]' + @CR_LF_TAB_2
+ '[,@DOC_TYPE = @DOC_TYPE]' + @CR_LF_TAB_2
+ '[,@SORT_CHOICE = @SORT_CHOICE]' + @CR_LF_TAB_2
+ '[,@SORT_DESC = @SORT_DESC]' + @CR_LF_TAB_2
+ '[,@TEST = @test-2]' + @CR_LF_3_TAB
+ '@PIPELINES_ID:' + @CR_LF_TAB_2
+ 'There must be a valid pipeline identifier.' + @CR_LF_3_TAB
+ '@PAGE:' + @CR_LF_TAB_2
+ 'Part flag, part input and output parameter. Passing in null results in population of temporary storage,' + @CR_LF_TAB_2
+ 'then outputs the number of pages. Or, pass a valid page number, and it will return a page from temporary storage' + @CR_LF_3_TAB
+ '@ROWS_PER_PAGE:' + @CR_LF_TAB_2
+ 'If @PAGE is null, than you have to pass a page size for page calculations.' + @CR_LF_3_TAB
+ '@DOC_TYPE:' + @CR_LF_TAB_2
+ 'Defaults to null, or enter a valid value to filter by doc type' + @CR_LF_3_TAB
+ '@SORT_CHOICE:' + @CR_LF_TAB_2
+ '0 -- Default sort is by barcode id' + @CR_LF_TAB_2
+ '1 -- sort by document date' + @CR_LF_3_TAB
+ '@SORT_DESC:' + @CR_LF_TAB_2
+ '0 -- Default sort is ascending' + @CR_LF_TAB_2
+ '1 -- Sort descending' + @CR_LF_3_TAB
IF @test-2 = 1
BEGIN
PRINT '@FAIL length = ' + CAST( LEN( @FAIL ) AS VARCHAR )
PRINT '@USAGE length = ' + CAST( LEN( @USAGE ) AS VARCHAR )
END
-- USP_RPT_DOCUMENTS_PAGING
IF @PIPELINES_ID IS NULL
BEGIN
PRINT @USAGE
RETURN @@ERROR
END
IF @PAGE IS NULL AND @ROWS_PER_PAGE IS NULL
BEGIN
PRINT @FAIL + '@ROWS_PER_PAGE is null. Need rows per page to calculate number of pages.'
PRINT @USAGE
RETURN @USER_DEFINED_ERROR
END
IF NOT EXISTS ( SELECT * FROM TBL_DOCUMENTS WHERE PIPELINES_ID = @PIPELINES_ID )
BEGIN
PRINT @FAIL + '@PIPELINES_ID = ' + CAST( @PIPELINES_ID AS VARCHAR ) + ' not found in TBL_DOCUMENTS'
PRINT @USAGE
RETURN @USER_DEFINED_ERROR
END
IF @DOC_TYPE IS NOT NULL
BEGIN
IF NOT EXISTS ( SELECT * FROM TBL_DOCUMENTS WHERE PIPELINES_ID = @PIPELINES_ID AND DOC_TYPE = @DOC_TYPE )
BEGIN
PRINT @FAIL + '@DOC_TYPE = ' + @DOC_TYPE + ' not associated with this pipeline in TBL_DOCUMENTS'
PRINT @USAGE
RETURN @USER_DEFINED_ERROR
END
END
IF @SORT_CHOICE NOT BETWEEN 0 AND 1
BEGIN
PRINT @FAIL + '@SORT_CHOICE out of range.'
PRINT @USAGE
RETURN @USER_DEFINED_ERROR
END
/***********************************************************************************************************
Initialize temporary storage and determine number of pages
***********************************************************************************************************/
IF @PAGE IS NULL
BEGIN
BEGIN TRANSACTION intitialize_paging_table
SET @SQL_STRING =
'CREATE TABLE ##TBL_DOCUMENTS_PAGED_' + CAST( @PIPELINES_ID AS VARCHAR ) + '(' + @CR_LF_TAB
+ 'DOCUMENTS_ID INT' + @CR_LF_TAB
+ ',ROW_ID INT IDENTITY' + @CR_LF
+ ')'
IF @test-2 = 1
BEGIN
PRINT @SQL_STRING
PRINT '@SQL_STRING length = ' + CAST( LEN( @SQL_STRING ) AS VARCHAR )
END
SET @SQL_STRING = dbo.UFN_TRIM( @SQL_STRING )
EXECUTE( @SQL_STRING )
SELECT @ERROR = @@ERROR
IF @ERROR <> @NO_ERROR
BEGIN
PRINT @FAIL + 'CREATE TABLE ##TBL_DOCUMENTS_PAGED_' + CAST( @PIPELINES_ID AS VARCHAR ) + '; @ERROR = ' + CAST( @ERROR AS VARCHAR )
ROLLBACK TRANSACTION intitialize_paging_table
RETURN @ERROR
END
DELETE FROM
TBL_DOCUMENTS_PAGED
WHERE
PIPELINES_ID = @PIPELINES_ID
SET @INSERT_STRING =
'INSERT INTO TBL_DOCUMENTS_PAGED(' + @CR_LF_TAB_3
+ 'DOCUMENTS_ID' + @CR_LF_TAB_3
+ ',PIPELINES_ID' + @CR_LF_TAB_3
+ ',ORIGINAL_PIPELINE_NO' + @CR_LF_TAB_3
+ ',BARCODE_ID' + @CR_LF_TAB_3
+ ',DOC_NAME' + @CR_LF_TAB_3
+ ',DOC_DATE' + @CR_LF_TAB_3
+ ',DOC_TYPE' + @CR_LF_TAB_3
+ ',IMAGE_PATH' + @CR_LF_TAB_3
+ ',ROW_INFO' + @CR_LF_TAB_3
+ ',IMAGE_ILLEGIBLE' + @CR_LF_TAB_3
+ ',REVIEWED' + @CR_LF_TAB_3
+ ',GEN' + @CR_LF_TAB_3
+ ',ROW' + @CR_LF_TAB_3
+ ',LIA' + @CR_LF_TAB_3
+ ',CON' + @CR_LF_TAB_3
+ ',OPS' + @CR_LF_TAB_3
+ ',USERS_ID )' + @CR_LF_TAB
+ 'SELECT' + @CR_LF_TAB_2
+ 'DOCUMENTS_ID' + @CR_LF_TAB_2
+ ',PIPELINES_ID' + @CR_LF_TAB_2
+ ',ORIGINAL_PIPELINE_NO' + @CR_LF_TAB_2
+ ',BARCODE_ID' + @CR_LF_TAB_2
+ ',DOC_NAME' + @CR_LF_TAB_2
+ ',DOC_DATE' + @CR_LF_TAB_2
+ ',DOC_TYPE' + @CR_LF_TAB_2
+ ',IMAGE_PATH' + @CR_LF_TAB_2
+ ',ROW_INFO' + @CR_LF_TAB_2
+ ',IMAGE_ILLEGIBLE' + @CR_LF_TAB_2
+ ',REVIEWED' + @CR_LF_TAB_2
+ ',GEN' + @CR_LF_TAB_2
+ ',ROW' + @CR_LF_TAB_2
+ ',LIA' + @CR_LF_TAB_2
+ ',CON' + @CR_LF_TAB_2
+ ',OPS' + @CR_LF_TAB_2
+ ',USERS_ID' + @CR_LF_TAB_2
+ 'FROM TBL_DOCUMENTS' + @CR_LF_TAB
SET @WHERE_STRING = 'WHERE PIPELINES_ID = ' + CAST( @PIPELINES_ID AS VARCHAR ) + @CR_LF_TAB
IF @DOC_TYPE IS NOT NULL
BEGIN
SET @WHERE_STRING = @WHERE_STRING + ' AND DOC_TYPE = ''' + @DOC_TYPE + '''' + @CR_LF_TAB
END
IF @SORT_CHOICE = 0
BEGIN
SET @ORDER_BY_STRING = 'ORDER BY BARCODE_ID, DOCUMENTS_ID'
END
ELSE IF @SORT_CHOICE = 1
BEGIN
SET @ORDER_BY_STRING = 'ORDER BY DOC_DATE, DOCUMENTS_ID'
END
-- If you need more sort choices add them here, to usage, and to the "out of range" check.
-- ELSE IF @SORT_CHOICE = 2
-- BEGIN
-- SET @ORDER_BY_STRING = 'ORDER BY ??????'
-- END
IF @SORT_DESC = 1
BEGIN
SET @ORDER_BY_STRING = STUFF( @ORDER_BY_STRING, CHARINDEX( ',', @ORDER_BY_STRING ), 0, ' DESC' )
END
SET @SQL_STRING = @INSERT_STRING + @WHERE_STRING + @ORDER_BY_STRING
IF @test-2 = 1
BEGIN
PRINT @SQL_STRING
PRINT '@INSERT_STRING length = ' + CAST( LEN( @INSERT_STRING ) AS VARCHAR )
PRINT '@WHERE_STRING length = ' + CAST( LEN( @WHERE_STRING ) AS VARCHAR )
PRINT '@ORDER_BY_STRING length = ' + CAST( LEN( @ORDER_BY_STRING ) AS VARCHAR )
PRINT '@SQL_STRING length = ' + CAST( LEN( @SQL_STRING ) AS VARCHAR )
END
SET @SQL_STRING = dbo.UFN_TRIM( @SQL_STRING )
EXECUTE( @SQL_STRING )
SELECT @ERROR = @@ERROR
IF @ERROR <> @NO_ERROR
BEGIN
PRINT @FAIL + 'Insert into TBL_DOCUMENTS_PAGED; @ERROR = ' + CAST( @ERROR AS VARCHAR )
ROLLBACK TRANSACTION intitialize_paging_table
RETURN @ERROR
END
-- SELECT * FROM TBL_DOCUMENTS_PAGED
SET @INSERT_STRING =
'INSERT INTO ##TBL_DOCUMENTS_PAGED_' + CAST( @PIPELINES_ID AS VARCHAR ) + '( DOCUMENTS_ID )' + @CR_LF_TAB
+ 'SELECT DOCUMENTS_ID FROM TBL_DOCUMENTS_PAGED' + @CR_LF_TAB
SET @SQL_STRING = @INSERT_STRING + @WHERE_STRING + @ORDER_BY_STRING
IF @test-2 = 1
BEGIN
PRINT @SQL_STRING
PRINT '@SQL_STRING length = ' + CAST( LEN( @SQL_STRING ) AS VARCHAR )
END
SET @SQL_STRING = dbo.UFN_TRIM( @SQL_STRING )
EXECUTE( @SQL_STRING )
SELECT @ERROR = @@ERROR
IF @ERROR <> @NO_ERROR
BEGIN
PRINT @FAIL + 'Insert into @TBL_DOCUMENTS_PAGED; @ERROR = ' + CAST( @ERROR AS VARCHAR )
ROLLBACK TRANSACTION intitialize_paging_table
RETURN @ERROR
END
SET @SQL_STRING =
'UPDATE DP' + @CR_LF
+ 'SET DP.ROW_ID = TDP.ROW_ID' + @CR_LF_TAB
+ ',DP.PAGE_ID = ( TDP.ROW_ID + ' + CAST( @ROWS_PER_PAGE AS VARCHAR )
+ ' - 1 ) / ' + CAST( @ROWS_PER_PAGE AS VARCHAR ) + @CR_LF
+ 'FROM ##TBL_DOCUMENTS_PAGED_' + CAST( @PIPELINES_ID AS VARCHAR ) + ' AS TDP' + @CR_LF
+ 'INNER JOIN TBL_DOCUMENTS_PAGED AS DP' + @CR_LF
+ 'ON TDP.DOCUMENTS_ID = DP.DOCUMENTS_ID'
IF @test-2 = 1
BEGIN
PRINT @SQL_STRING
PRINT '@SQL_STRING length = ' + CAST( LEN( @SQL_STRING ) AS VARCHAR )
END
SET @SQL_STRING = dbo.UFN_TRIM( @SQL_STRING )
EXECUTE( @SQL_STRING )
SELECT @ERROR = @@ERROR
IF @ERROR <> @NO_ERROR
BEGIN
PRINT @FAIL + 'Update TBL_DOCUMENTS_PAGED.ROW_ID and PAGE_ID; @ERROR = ' + CAST( @ERROR AS VARCHAR )
ROLLBACK TRANSACTION intitialize_paging_table
RETURN @ERROR
END
SELECT
@PAGE = MAX( PAGE_ID )
FROM
TBL_DOCUMENTS_PAGED
WHERE
PIPELINES_ID = @PIPELINES_ID
SET @SQL_STRING = 'DROP TABLE ##TBL_DOCUMENTS_PAGED_' + CAST( @PIPELINES_ID AS VARCHAR )
EXECUTE ( @SQL_STRING )
SELECT @ERROR = @@ERROR
IF @ERROR <> @NO_ERROR
BEGIN
PRINT @FAIL + 'DROP TABLE ##TBL_DOCUMENTS_PAGED_' + CAST( @PIPELINES_ID AS VARCHAR )
+ '; @ERROR = ' + CAST( @ERROR AS VARCHAR )
ROLLBACK TRANSACTION intitialize_paging_table
RETURN @ERROR
END
COMMIT TRANSACTION intitialize_paging_table
END
ELSE
BEGIN
SELECT
DOCUMENTS_ID
,PIPELINES_ID
,ORIGINAL_PIPELINE_NO
,BARCODE_ID
,DOC_TYPE
,DOC_NAME
,DOC_DATE
,ROW_INFO
,IMAGE_ILLEGIBLE
,REVIEWED
,IMAGE_PATH
,USERS_ID
,GEN
,ROW
,LIA
,CON
,OPS
FROM
TBL_DOCUMENTS_PAGED
WHERE
PIPELINES_ID = @PIPELINES_ID
AND
PAGE_ID = @PAGE
ORDER BY
ROW_ID
END
RETURN @@ERROR
END
March 2, 2006 at 6:36 pm
You could use this SP
This SP is paging 20 rows per page...if you want to change how many rows in one page, just change the number '20' in this SP to the number you like
create procedure Paging(@IndexPage int)
as
begin
set nocount on
declare @strQ varchar(5000)
declare @PageLower int
declare @PageUpper int
set @PageLower = @IndexPage * 20
set @PageUpper = (@IndexPage * 20)+20
set @strQ = 'select id,fieldA,fieldB, fieldC from
(select TOP ' + cast(@PageUpper as varchar(8)) +
' dbo.tblName.id,dbo.tblName.fieldA, dbo.tblName.fieldB,dbo.tblName.fieldC
from dbo.tblName)as t1
where not exists (select * from
(select TOP ' + cast(@PageLower as varchar(8)) +
' dbo.tblName.id,dbo.tblName.fieldA, dbo.tblName.fieldB,dbo.tblName.fieldC
from dbo.tblName)as t2
where t1.id=t2.id)'
execute(@strQ)
end
/* to execute this SP, use this statement
exec Paging 0 --> SP will return the first 20(1-20) recordsets
exec Paging 1 --> SP will return the recordsets 21 - 40
exec Paging 2 --> SP will return the recordsets 41 - 60
*/
Thanks...
alexia
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply