DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
 
 
SITE SEARCH SQLUSA.com VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
How to return the nth page of a result set?

Execute the following SQL Server Transact-SQL scripts in Management Studio Query Editor to demonstrate paging in query results. Typical use: paging through information on a web page.

-- SQL page results - SQL page recordset - SQL page records

USE tempdb

GO

SET nocount  ON

-- SQL create table

CREATE TABLE PayRegister (

  PayRegisterID INT    IDENTITY    PRIMARY KEY, -- key

  EmployeeID    INT,                            -- key (FK)

  Pay           MONEY,                          -- data

  PayCheckDate  SMALLDATETIME,                  -- data

  ModifiedDate  DATETIME default (getdate()))   -- row maintenance

GO

-- SQL insert into table

-- SQL insert values

-- SQL populate table

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (525, 1157.23,'1/1/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (125, 13257.23,'1/1/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (225, 14257.23,'1/1/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (325, 15257.23,'1/1/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (425, 17257.23,'1/1/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (555, 1257.13,'1/2/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (545, 7357.18,'1/9/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (855, 7357.18,'1/10/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (555, 9257.30,'1/21/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (551, 6257.30,'1/21/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (595, 1257.15,'1/25/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (255, 1057.99,'2/4/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (553, 3457.23,'2/7/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (585, 3457.87,'2/12/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (555, 5857.34,'2/15/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (955, 13457.23,'2/25/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (155, 9857.26,'3/1/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (255, 4557.32,'3/15/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (355, 4757.30,'3/16/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (455, 3557.25,'3/16/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (655, 15757.23,'3/16/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (575, 2457.56,'3/31/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (585, 3457.23,'4/4/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (595, 2357.99,'4/5/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (350, 5657.35,'4/8/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (455, 14757.53,'6/5/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (575, 3057.56,'7/7/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (535, 6557.34,'7/8/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (255, 4457.55,'8/5/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (585, 18457.25,'8/5/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (595, 2357.56,'8/22/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (255, 4557.77,'9/15/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (545, 6557.95,'9/26/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (525, 16557.95,'9/26/2017')

INSERT PayRegister(EmployeeID, Pay, PayCheckDate) VALUES (544, 26557.95,'9/26/2017')

GO

 

-- SQL page query results

DECLARE  @PageSize INT,

         @PageNo   INT

SET @PageSize = 5

SET @PageNo = 3

-- SQL row_number over order by - row number, row id

-- SQL between operator

SELECT PayRegisterID,

         EmployeeID,

-- SQL currency formatting

         '$'+convert(varchar,Pay,1) AS Pay,

       PaycheckDate,

         RowNumber

FROM   (SELECT *,

               Row_number()

                 OVER(ORDER BY PayRegisterID) AS RowNumber

        FROM   PayRegister) AS a

WHERE  RowNumber BETWEEN (@PageNo - 1) * @PageSize + 1 AND (@PageNo * @PageSize)

SET nocount  OFF

GO

/* Results

 

PayRegisterID     EmployeeID  Pay         PaycheckDate            RowNumber

11                595         $1,257.15   2017-01-25 00:00:00     11

12                255         $1,057.99   2017-02-04 00:00:00     12

13                553         $3,457.23   2017-02-07 00:00:00     13

14                585         $3,457.87   2017-02-12 00:00:00     14

15                555         $5,857.34   2017-02-15 00:00:00     15

*/

 

-- Cleanup

drop table tempdb.dbo.PayRegister

 

Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE