Monday 18 August 2008

Nice paging stored procedure in MS SQL T-SQL

This also demonstrates the use of the WITH heyword.

CREATE PROCEDURE [dbo].[Employee_Paged] ( @maximumRows int = 10, @startRowIndex int = 1 ) AS

BEGIN

SET NOCOUNT ON;

WITH [EmployeeByPage] AS

(

SELECT

row_number() OVER (ORDER BY Employee.Id ASC) AS rowid,

Employee.Id,

Employee.Name

FROM

Employee

)

SELECT

Paged.Id,

Paged.Name

FROM

[EmployeeByPage] AS Paged

WHERE

Paged.rowid BETWEEN @startRowIndex AND @startRowIndex + @maximumRows

END


Source: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3263486&SiteID=1

No comments: