Pagination of grid with stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Pagination of grid with stored proc

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2008-03-24 : 06:50:48

Following stored proc is for the pupose of pagination in grid .what must be going wrong with dynamic sql.The part highlighted in red in responsible for pagination
?
Following code works fine for CurrentPage=1
but for CurrentPage=2 following query does not give the right output
For example for Projectbenefit id=1
Total records=6
NumberOfRecordsPerPage=5
So for CurrentPage=2
6 th Record should be shown.
But no record is shown for second page

SELECT @SQLFinal = @Select+
+ ' AND T1.AssessBenefitID NOT IN (SELECT T1.AssessBenefitID FROM ( SELECT DISTINCT TOP '
+ CONVERT(varchar(5), @NumberOfRecords * (@CurrentPage - 1))
+' T1.AssessBenefitID '+@LsOrderBy+@From+ ' Order By '+@OrderBy
+ ' ) InnerTable) '
+' Order By '+ @OrderByEND

ALTER PROCEDURE [dbo].[spPMPT_GetProj]

@ProjectBenefitID INT,

@OrderBY VARCHAR(40),
-- Parmeters for Paging [Start]
@TotalPages INT OUT ,
@CurrentPageNumber INT OUT ,
@NumberOfRecords INT = 5 , /*PageSize*/
@CurrentPage INT = 0 /*PageNumber*/

-- Parmeters for Paging [End]

AS

SET NOCOUNT ON

DECLARE @TMP FLOAT
DECLARE @ErrorMsgID INT
DECLARE @ErrorMsg VARCHAR(200)

----- Paging declarations start
DECLARE @SQLFinal NVARCHAR(4000)
DECLARE @Count INT
DECLARE @SC VARCHAR(4000)
----- Paging declarations end

DECLARE @Select AS VARCHAR(4000)
DECLARE @From AS VARCHAR(4000)
DECLARE @Where AS VARCHAR(4000)
DECLARE @LsOrderBy AS VARCHAR(4000)

-- Initialize vars
SET @SC = ''
SET @From = ''
SET @Where = ''
SET @Select = ''
SET @SQLFinal = ''
SET @Count = 0

IF (@CurrentPage = 0 OR @CurrentPage IS NULL)
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. The Page Number cannot be zero.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END
IF (@NumberOfRecords = 0 OR @NumberOfRecords IS NULL )
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. Number of records per page cannot be zero.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END
IF (@Orderby IS NULL OR @Orderby = '')
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. The Order by cannot be null.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END

CREATE TABLE #TEMP_BENEFIT1
(

AssessBenefitID INT,
ProjectBenefitID INT,
ExpectedQuantity INT,
ExpectedQuality VARCHAR(2000),
Comments VARCHAR(2000)
)

INSERT INTO #TEMP_BENEFIT1 SELECT AssessBenefitID,ProjectBenefitID,
Quantity,Quality,
Comments
FROM PMPT_AssessBenefit
WHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='E' --and AssessBenefitID=@IterationID

CREATE TABLE #TEMP_BENEFIT2
(

AssessBenefitID INT,
ProjectBenefitID INT,
ActualQuantity INT,
QtyFileID INT,
QtyFileName VARCHAR(100),
QtyFilepath VARCHAR(100),
ActualQuality VARCHAR(2000),
QuaFileID INT,
QualFileName VARCHAR(100),
QualFilepath VARCHAR(100),
Comments VARCHAR(2000),
refAssessBenefitID INT,
DateasON DATETIME
)

INSERT INTO #TEMP_BENEFIT2 SELECT PAB.AssessBenefitID,PAB.ProjectBenefitID,
PAB.Quantity,pab.qtyFileID,
(SELECT FileName FROM PMPT_Files WHERE FileID = pab.qtyFileID) as QtyFileName,
(SELECT UploadedFilePath FROM PMPT_Files WHERE FileID = pab.qtyFileID) as QtyFilepath,
PAB.Quality,pab.quaFileID,
(SELECT FileName FROM PMPT_Files WHERE FileID = pab.quaFileID) AS QualFileName,
(SELECT UploadedFilePath FROM PMPT_Files WHERE FileID = pab.quaFileID) as QuaFilepath,
PAB.Comments,PAB.refEXPAssessBenefitID,PAB.DateasON
FROM PMPT_AssessBenefit PAB
WHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='A'

DECLARE @UNIT VARCHAR(100)
SELECT @UNIT=NAME FROM PMPT_Picklists WHERE PicklistID = (SELECT unitID FROM PMPT_ProjectBenefits WHERE ProjectBenefitID=@ProjectBenefitID)

IF @UNIT IS NULL
SET @UNIT = ''
SET @Select='
DECLARE @UNIT VARCHAR(100)
SELECT @UNIT=NAME FROM PMPT_Picklists WHERE PicklistID = (SELECT unitID FROM PMPT_ProjectBenefits WHERE ProjectBenefitID='+CONVERT(VARCHAR(10),@ProjectBenefitID)+')
SELECT DISTINCT TOP ' + CONVERT(varchar(3), @NumberOfRecords) +
' T1.AssessBenefitID, CAST(T1.ExpectedQuantity AS VARCHAR)+'' ''+ @UNIT as ExpectedQuantity,
CAST( T2.ActualQuantity AS VARCHAR)+'' ''+ @UNIT as ActualQuantity, T2.QtyFileID, T2.QtyFileName AS QtyFileName ,T2.QtyFilepath, T1.ExpectedQuality AS ExpectedQuality , T2.ActualQuality AS ActualQuality ,
T2.QuaFileID,T2.QualFileName AS QualFileName ,T2.QualFilepath, T2.COMMENTS AS COMMENTS,CONVERT(VARCHAR(10),T2.DateasON,103) AS DateasON
FROM #TEMP_BENEFIT1 T1,#TEMP_BENEFIT2 T2
WHERE T1.AssessBenefitID = T2.refAssessBenefitID'

DECLARE @COUNTTEMP INT
SELECT @COUNTTEMP = COUNT(T1.AssessBenefitID)
FROM #TEMP_BENEFIT1 T1,#TEMP_BENEFIT2 T2
WHERE T1.AssessBenefitID = T2.refAssessBenefitID
PRINT @COUNTTEMP
--
-----------------------------Snippet for Paging [start] CREATE TABLE #TmpATFCnt(Cnt int)
-- SELECT @SC = 'INSERT INTO #TmpATFCnt(Cnt)VALUES('+ CONVERT(VARCHAR,@COUNTTEMP)+') '
-- print @SC

SELECT @SC = 'INSERT INTO #TmpATFCnt(Cnt)VALUES('+ CONVERT(VARCHAR,@COUNTTEMP)+') '
print @SC

EXEC (@SC)
SELECT @Count = Cnt FROM #TmpATFCnt

SET @TMP=(@Count % @NumberOfRecords )

IF (@TMP > 0)
SET @TotalPages=(@Count / @NumberOfRecords )+1

ELSE
SET @TotalPages=(@Count / @NumberOfRecords )
PRINT @TotalPages
DROP TABLE #TmpATFCnt
-- Default Value for CurrentPage number is 0 if there is not any record
SET @CurrentPageNumber = 0
-- If at least 1 record exists
IF (@Count > 0)
BEGIN
-- Verify that the records are not deleted and the records exist for
-- the Page Number requested for
-- If the available records are less than Page Size
IF (@NumberOfRecords > @Count)
BEGIN
SET @CurrentPage = 1
END
-- If the available records are just one less than the
-- desired start number
ELSE
IF ((@NumberOfRecords * (@CurrentPage - 1)) = @Count)
BEGIN
SET @CurrentPage = @CurrentPage - 1
END
-- If the available records are 2 or more less than the
-- desired start number
ELSE
IF ((@NumberOfRecords * (@CurrentPage - 1)) > @Count)
BEGIN
SET @CurrentPage = CEILING(CAST(@Count AS FLOAT) / CAST(@NumberOfRecords AS FLOAT))
END
-- Set the revised page number to out parameter

SET @CurrentPageNumber = @CurrentPage

END
-- ---------------------------Snippet for Paging [End]
---------------START for order by---------------
IF CHARINDEX(' ',@OrderBy) > 0
SET @LsOrderBy = SUBSTRING (@OrderBy,1,(CHARINDEX(' ',@OrderBy)))
ELSE
SET @LsOrderBy = @OrderBy

IF @LsOrderBy = 'ExpectedQuantity'
SET @LsOrderBy = ', T1.ExpectedQuantity AS ExpectedQuantity '
IF @LsOrderBy = 'ActualQuantity'
SET @LsOrderBy = ', T2.ActualQuantity AS ActualQuantity '
IF @LsOrderBy = ' QtyFileName'
SET @LsOrderBy = ', T2.QtyFileName AS QtyFileName '
IF @LsOrderBy = 'ExpectedQuality'
SET @LsOrderBy = ', T1.ExpectedQuality AS ExpectedQuality'
IF @LsOrderBy = 'ActualQuality'
SET @LsOrderBy = ', T2.ActualQuality AS ActualQuality '
IF @LsOrderBy = 'QualFileName'
SET @LsOrderBy = ', T2.QualFileName AS QualFileName'
IF @LsOrderBy = 'DateasON'
SET @LsOrderBy = ', T2.DateasON AS DateasON'
IF @LsOrderBy = 'COMMENTS'
SET @LsOrderBy = ', T2.COMMENTS AS COMMENTS'
---------------END for order by---------------
BEGIN
SELECT @SQLFinal = @Select+
+ ' AND T1.AssessBenefitID NOT IN (SELECT T1.AssessBenefitID FROM ( SELECT DISTINCT TOP '
+ CONVERT(varchar(5), @NumberOfRecords * (@CurrentPage - 1))
+' T1.AssessBenefitID '+@LsOrderBy+@From+ ' Order By '+@OrderBy
+ ' ) InnerTable) '
+' Order By '+ @OrderByEND

Print @SQLFinal
EXEC(@SQLFinal)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources