使用Row_Number的分页存储过程

分页存储过程是非常通用的功能，今天自己写了一个分页存储过程，写下来记录一下。

CREATE TABLE [dbo].[MyTable](
	[Id] [int] NOT NULL,
	[Info1] [nvarchar](50) NULL,
	[Info2] [nvarchar](50) NULL,
	[Info3] [nvarchar](50) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

分页存储过程

-- =============================================
-- Author:		justin
-- Create date: 2015-2-28
-- Description:	divide page
-- =============================================
ALTER PROCEDURE prc_divpage
	@table  nvarchar(50),
	@fields nvarchar(50),
	@where  nvarchar(50),
	@order  nvarchar(50),
	@index  int,
	@count  int
AS
BEGIN
	declare @start int;
	set @start = (@index-1) * @count;
	declare @sql nvarchar(max);
	set @sql = 'select ' + @fields + ' from '+
	'(select row_number() over (order by ' + @order+ ') as RowNumber,' + 
	@fields + ' from '+ @table +' where '+ @where +')'+
	'as r where r.RowNumber >' + CONVERT(nvarchar(20),@start) + 
	' and r.RowNumber < ' + CONVERT(nvarchar(20),@start+@count+1) + 
	' order by r.RowNumber';
	
	print @sql
	
	exec(@sql);
END
GO