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=1but for CurrentPage=2 following query does not give the right outputFor example for Projectbenefit id=1Total records=6NumberOfRecordsPerPage=5So for CurrentPage=26 th Record should be shown.But no record is shown for second pageSELECT @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 '+ @OrderByENDALTER 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]ASSET 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 endDECLARE @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=@IterationIDCREATE 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 DateasONFROM #TEMP_BENEFIT1 T1,#TEMP_BENEFIT2 T2 WHERE T1.AssessBenefitID = T2.refAssessBenefitID'DECLARE @COUNTTEMP INTSELECT @COUNTTEMP = COUNT(T1.AssessBenefitID)FROM #TEMP_BENEFIT1 T1,#TEMP_BENEFIT2 T2 WHERE T1.AssessBenefitID = T2.refAssessBenefitIDPRINT @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 = @CurrentPageEND-- ---------------------------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 '+ @OrderByENDPrint @SQLFinalEXEC(@SQLFinal) |
|