Using ROW_NUMBER() to paginate your data with SQL Server 2005 and ASP.NET

��/****** Object:  StoredProcedure [dbo].[sp_getzipcodes]    Script Date: 11/20/2005 12:47:31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:		JWA

-- Create date: 11.19.05

-- Description:	using rownumber

-- =============================================

CREATE PROCEDURE [dbo].[sp_getzipcodes] 

	-- Add the parameters for the stored procedure here

	@start int = 0

	

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	Set NOCOUNT ON



	SELECT TOP 20 * FROM 

	(

	SELECT zip,city,state,latitude,longitude,timezone,dst,

		   ROW_NUMBER() OVER (ORDER BY zip) AS num

		   FROM dbo.zipcode

	) AS a

	WHERE num > @start



END