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 HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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
Exam 70-461
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