使用Row_Number的分页存储过程

来源：互联网发布：大同seo大牛编辑：程序博客网时间：2024/05/17 04:07

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

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  intASBEGINdeclare @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 @sqlexec(@sql);ENDGO

0 0