Paging in SQL
-
This is my Query i want to implement paging where i will get the page size as a parameter from UI..How do i implement paging in this query..Plz any one Help.. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: -- Create date: <11/04/2012> -- Description: -- ============================================= --exec iSRPDb_Sp_Search_WO 1,0,'0','T',10,1 --select * from tblProject where ProjectID=13849 --select * from tblProject where ProjectID=22412 --select * from tblProject where ProjectCode='B20904' --select * from tblSubContractor where SubContractorID=88420 --select top 10* from tblWorkOrderHeader where ProjectID=22412 ALTER PROCEDURE [dbo].[iSRPDb_Sp_Search_WO] @pBusinessUnitID as int=0, @pProjectID as int, @pSubcontractorID as int, @pWONumber as varchar(10), @PageSize as int AS BEGIN SET NOCOUNT ON; DECLARE @BusinessUnitFilter Varchar(255) DECLARE @NameFilter Varchar(255) DECLARE @SubConNameFilter Varchar(255) DECLARE @WONumFilter Varchar(255) DECLARE @Filter Varchar(2000) DECLARE @StatusFilter VARCHAR(255) DECLARE @PageNumber int DECLARE @RowStart int DECLARE @RowEnd int DECLARE @condition varchar(255) SET @StatusFilter='' --if @PageNumber > 0 -- -- SET @PageNumber = @PageNumber -1 -- SET @RowStart = @PageSize * @PageNumber + 1; -- SET @RowEnd = @RowStart + @PageSize - 1 ; IF @pBusinessUnitID > 0 SET @BusinessUnitFilter = 'TWO. BusinessUnitID=' + Convert(Varchar(10),@pBusinessUnitID) ELSE SET @BusinessUnitFilter = '' IF LTRIM(RTRIM(@pProjectID)) > 0 SET @NameFilter = ' AND TP.ProjectID =' + Convert(Varchar(255),@pProjectID) ELSE SET @NameFilter = '' IF LTRIM(RTRIM(@pSubcontractorID)) > 0 SET @SubConNameFilter = ' AND TS.SubcontractorID =' + Convert(Varchar(255),@pSubcontractorID) ELSE SET @SubConNameFilter = '' If ltrim(rtrim(@pWONumber))<> '' Set @WONumFilter = 'AND TWO.WONumber LIKE ''%' + @pWONumber + '%''' Else Set @WONumFilter = '' SET @FILTER =@StatusFilter + @BusinessUnitFilter + @NameFilter + @SubConNameFilter + @WONumFilter exec('select TWO.WONumber, TS.Name AS SubcontractorName, TP.ProjectCode, TP.Description as ProjectName , TP.StartDate, TP.EndDate, ROW_NUMBER() OVER(order by TP.Description) as RowNO
-
This is my Query i want to implement paging where i will get the page size as a parameter from UI..How do i implement paging in this query..Plz any one Help.. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: -- Create date: <11/04/2012> -- Description: -- ============================================= --exec iSRPDb_Sp_Search_WO 1,0,'0','T',10,1 --select * from tblProject where ProjectID=13849 --select * from tblProject where ProjectID=22412 --select * from tblProject where ProjectCode='B20904' --select * from tblSubContractor where SubContractorID=88420 --select top 10* from tblWorkOrderHeader where ProjectID=22412 ALTER PROCEDURE [dbo].[iSRPDb_Sp_Search_WO] @pBusinessUnitID as int=0, @pProjectID as int, @pSubcontractorID as int, @pWONumber as varchar(10), @PageSize as int AS BEGIN SET NOCOUNT ON; DECLARE @BusinessUnitFilter Varchar(255) DECLARE @NameFilter Varchar(255) DECLARE @SubConNameFilter Varchar(255) DECLARE @WONumFilter Varchar(255) DECLARE @Filter Varchar(2000) DECLARE @StatusFilter VARCHAR(255) DECLARE @PageNumber int DECLARE @RowStart int DECLARE @RowEnd int DECLARE @condition varchar(255) SET @StatusFilter='' --if @PageNumber > 0 -- -- SET @PageNumber = @PageNumber -1 -- SET @RowStart = @PageSize * @PageNumber + 1; -- SET @RowEnd = @RowStart + @PageSize - 1 ; IF @pBusinessUnitID > 0 SET @BusinessUnitFilter = 'TWO. BusinessUnitID=' + Convert(Varchar(10),@pBusinessUnitID) ELSE SET @BusinessUnitFilter = '' IF LTRIM(RTRIM(@pProjectID)) > 0 SET @NameFilter = ' AND TP.ProjectID =' + Convert(Varchar(255),@pProjectID) ELSE SET @NameFilter = '' IF LTRIM(RTRIM(@pSubcontractorID)) > 0 SET @SubConNameFilter = ' AND TS.SubcontractorID =' + Convert(Varchar(255),@pSubcontractorID) ELSE SET @SubConNameFilter = '' If ltrim(rtrim(@pWONumber))<> '' Set @WONumFilter = 'AND TWO.WONumber LIKE ''%' + @pWONumber + '%''' Else Set @WONumFilter = '' SET @FILTER =@StatusFilter + @BusinessUnitFilter + @NameFilter + @SubConNameFilter + @WONumFilter exec('select TWO.WONumber, TS.Name AS SubcontractorName, TP.ProjectCode, TP.Description as ProjectName , TP.StartDate, TP.EndDate, ROW_NUMBER() OVER(order by TP.Description) as RowNO
You don't say which SQL system this is, but assuming SQL Server. I'd start at the basic, and use
BETWEENsomehow. i.e. one of yourWHEREclauses in the proc needs to be similar to:WHERE [IDColumn] BETWEEN @StartID AND @EndID
Obviously @StartID and @EndID would be passed in. Assuming IDColumn is an sequential and consecutive field this will return a defined number of rows. In SQLServer 2008 (maybe 2005), they introduced RowNum (like Oracle), which would be more consistent, and wouldn't rely on an sequential field in the table. That's where I'd start at least.
-
You don't say which SQL system this is, but assuming SQL Server. I'd start at the basic, and use
BETWEENsomehow. i.e. one of yourWHEREclauses in the proc needs to be similar to:WHERE [IDColumn] BETWEEN @StartID AND @EndID
Obviously @StartID and @EndID would be passed in. Assuming IDColumn is an sequential and consecutive field this will return a defined number of rows. In SQLServer 2008 (maybe 2005), they introduced RowNum (like Oracle), which would be more consistent, and wouldn't rely on an sequential field in the table. That's where I'd start at least.
Thank You For Your Response...AnyWay I Have FigureOut the Solution....After implementing Paging the SQL Server Query Looks Like this: I Have created a temporary table and inserted data to that table...and from there i calculated the pagenumber and size..the query will explain more..Hope this will help somebody... :) set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: -- Create date: <11/04/2012> -- Description: -- ============================================= --exec iSRPDb_Sp_Search_WO 1,0,'0','T',10,2 --select * from tblProject where ProjectID=13849 --select * from tblProject where ProjectID=22412 --select * from tblProject where ProjectCode='B20904' --select * from tblSubContractor where SubContractorID=88420 --select top 10* from tblWorkOrderHeader where ProjectID=22412 ALTER PROCEDURE [dbo].[iSRPDb_Sp_Search_WO] @pBusinessUnitID as int=0, @pProjectID as int, @pSubcontractorID as int, @pWONumber as varchar(10), @PageSize as int, @PageNumber INT AS BEGIN SET NOCOUNT ON; DECLARE @BusinessUnitFilter Varchar(255) DECLARE @NameFilter Varchar(255) DECLARE @SubConNameFilter Varchar(255) DECLARE @WONumFilter Varchar(255) DECLARE @Filter Varchar(2000) DECLARE @StatusFilter VARCHAR(255) DECLARE @RowStart int DECLARE @RowEnd int DECLARE @condition varchar(255) SET @StatusFilter='' IF @pBusinessUnitID > 0 SET @BusinessUnitFilter = 'TWO. BusinessUnitID=' + Convert(Varchar(10),@pBusinessUnitID) ELSE SET @BusinessUnitFilter = '' IF LTRIM(RTRIM(@pProjectID)) > 0 SET @NameFilter = ' AND TP.ProjectID =' + Convert(Varchar(255),@pProjectID) ELSE SET @NameFilter = '' IF LTRIM(RTRIM(@pSubcontractorID)) > 0 SET @SubConNameFilter = ' AND TS.SubcontractorID =' + Convert(Varchar(255),@pSubcontractorID) ELSE SET @SubConNameFilter = '' If ltrim(rtrim(@pWONumber))<> '' Set @WONumFilter = 'AND TWO.WONumber LIKE ''%' + @pWONumber + '%''' Else Set @WONumFilter = '' SET @FILTER =@StatusFilter + @BusinessUnitFilter + @NameFilter + @SubConNameFilter + @WONumFilter CREATE TABLE #TempTable ( RowNumber bigint , WONumber varchar(250) , Name varchar(250) , ProjectCode varchar(250) , Description varchar(250) , StartDate datetime , EndDate dat