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 paginate company contact information?

Execute the following SQL Server Transact-SQL script in SSMS Query Editor to create pagination for the Person.Contact table info by applying the ROW_NUMBER() function.

-- SQL Server stored procedure - SQL pagination - SQL row_number function

CREATE PROCEDURE uspContactInfoByPage

                @PageSize   INT,

                @PageNumber INT

AS

  BEGIN

    WITH cteContact

         AS (SELECT ContactID,

                    FirstName,

                    MiddleName,

                    LastName,

                    EmailAddress,

                    Phone,

                    ROW_NUMBER()

                      OVER(ORDER BY LastName, FirstName, ContactID) AS SEQUENCE

             FROM   Person.Contact)

    SELECT Name = replace(FirstName + ' ' + isnull(MiddleName,'') + ' ' + LastName,

                          ' ',' '),

           [Email Address] = EmailAddress,

           Telephone = Phone

    FROM   cteContact

    WHERE  SEQUENCE BETWEEN @PageSize * @PageNumber + 1

    AND @PageSize * (@PageNumber + 1)

  END

 

GO

 

EXEC dbo.uspContactInfoByPage

  50 ,

  100

GO

 

/* Partial results

 

Name              Email Address                       Telephone

Kate  Becker      [email protected]          1 (11) 500 555-0141

Kate  Becker      [email protected]          1 (11) 500 555-0141

Kate  Becker      [email protected]         NULL

Kate  Becker      [email protected]         NULL

Kathryn  Becker   [email protected]       1 (11) 500 555-0110

*/

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