SQL Server - in storeprocedure per page i should get 10 rows per page

Asked By shailesh reddy on 12-Nov-11 02:04 PM

@TimeStamp1 DateTime,
@TimeStamp2 DateTime

AS
BEGIN
SET NOCOUNT ON;


SELECT     ExceptionId, ServerName, ProjectName, Message, DebugInfo, StackTrace, TimeStamp
FROM         Exceptions

Where TimeStamp Between @TimeStamp1 And @TimeStamp2




hi  actually i have 19 rows  b/w timestamp1 , timestamp2   plz anybody help me how to bring  10 rows per page.....plz
I have to bring only 10 rows using these condition ,plz  correct  me store procedure

Neha Garg replied to shailesh reddy on 12-Nov-11 02:36 PM
Hello Shailesh,

You can achieve it by using CEILING((ROW_NUMBER(). See the Example mentioned below to get the 10 rows per Page:

CREATE PROCEDURE Paging_Customers
(
  @SelectedPage int,
  @PageSize int
)
AS
BEGIN
  WITH CTE_Customers(ServerName, ProjectName, Message, DebugInfo, StackTrace, TimeStamp)
  AS
  (
  SELECT CEILING((ROW_NUMBER() OVER
  (ORDER BY CompanyName ASC
  AS PageNumber, ContactTitle, ContactName, CompanyName, Phone, Country
  FROM Exceptions
  )
 
SELECT *
FROM CTE_Customers WHERE PageNumber = @SelectedPage
END


Then, we call this procedure with this simple line:

EXEC Paging_Customers 3, 10

Kirtan Patel replied to shailesh reddy on 12-Nov-11 10:59 PM
Write Stored Procedure like below

@TimeStamp2 DateTime
AS
BEGIN
	SET NOCOUNT ON;
	SELECT TOP 10 
		ExceptionId, 
		ServerName, 
		ProjectName,
		[Message], 
		DebugInfo, 
		StackTrace,
		[TimeStamp] FROM Exceptions WHERE [TimeStamp] Between @TimeStamp1 And @TimeStamp2
END 
Suchit shah replied to shailesh reddy on 13-Nov-11 01:33 AM
The following procedure is useful to retrieve records in sets for displaying and useful in implementing custom paging.
Let us consider an Employees Table which doesn't have an Identity Column and we want to fetch sets of records based on the set requested for. Consider the following procedure

CREATE PROCEDURE uspPaging
@nStartValue INT,
@nEndValue INT
AS
SET NOCOUNT ON
DECLARE @tblTempData TABLE
(
nID INT IDENTITY,
EmployeeID INT,
LastName VARCHAR(50),
FirstName VARCHAR(50),
SupervisorID NCHAR(5)
)
INSERT INTO @tblTempData
(
EmployeeID,
LastName,
FirstName,
SupervisorID
)
SELECT
EmployeeID,
LastName,
FirstName,
ReportsTo
FROM Employees
ORDER BY
EmployeeID,
FirstName

SELECT EmployeeID,
LastName,
FirstName,
SupervisorID
FROM @tblTempData
WHERE nID BETWEEN @nStartValue AND @nEndValue
ORDER BY
nID ASC

The above procedure will accept 2 parameters @nStartValue and @nEndValue and display the records based on the values. The @tblTempData which we create within this procedure is useful in enforcing the IDENTITY Column which does not exist in the original table.
This procedure can be used when implementing custom paging and can be modified according to the actual requirements.

Suchit shah replied to shailesh reddy on 13-Nov-11 01:34 AM

SQL Server 2005 has a ROW_NUMBER Function that can help with paging records for you database applications.  ROW_NUMBER returns a sequential number, starting at 1, for each row returned in a resultset.

If I want the first page of 10 records from my log file sorted by Date DESC, I can use the ROW_NUMBER FUNCTION as follows:

 

SELECT  Description, Date
FROM     (SELECT  ROW_NUMBER() OVER (ORDER BY Date DESC)
             AS Row, Description, Date FROM LOG)
            AS LogWithRowNumbers
WHERE  Row >= 1 AND Row <= 10

 

The second page of 10 records would then be as follows:

 

SELECT  Description, Date
FROM     (SELECT  ROW_NUMBER() OVER (ORDER BY Date DESC)
             AS Row, Description, Date FROM LOG)
            AS LogWithRowNumbers
WHERE  Row >= 11 AND Row <= 20

 

If you have a lot of records, using TOP X in the inner SELECT clause may speed up things a bit as there is no use returning 1000 records if you are only going to grab records 11 through 20:

 

SELECT  Description, Date
FROM     (SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY Date DESC)
             AS Row, Description, Date FROM LOG)
            AS LogWithRowNumbers
WHERE  Row >= 11 AND Row <= 20

 

We can rap this up in a Stored Procedure as follows:

 

CREATE PROCEDURE dbo.ShowLog
    @PageIndex INT, 
    @PageSize INT 
AS

BEGIN 

WITH LogEntries AS ( 
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description FROM LOG) SELECT Date, Description FROM LogEntries WHERE Row between

(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

END