在编写分页存储过程前我们先为数据库创建一个测试表，这个测试表明叫做order，当中有3个字段，分别是or_id,orName,dateSta;下面创建表脚本：

CREATE TABLE [dbo].[Orders](
    [or_id] [int] IDENTITY(1,1) NOT NULL,
    [orName] [nvarchar](50) NOT NULL,
    [dateSta] [datetime] NOT NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
    [or_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
EXEC sys.sp_addextendedproperty @name=N'MS_Deｓｃｒｉｐｔion', @value=N'写入时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Orders', @level2type=N'COLUMN',@level2name=N'dateSta'
GO
SET IDENTITY_INSERT [dbo].[Orders] ON
INSERT [dbo].[Orders] ([or_id], [orName], [dateSta]) VALUES (1, N'naoqiu.com', CAST(0x0000A03700FEF029 AS DateTime))
INSERT [dbo].[Orders] ([or_id], [orName], [dateSta]) VALUES (2, N'naoqiu.com', CAST(0x0000A03700FF5BE8 AS DateTime))
INSERT [dbo].[Orders] ([or_id], [orName], [dateSta]) VALUES (3, N'押金', CAST(0x0000A03700FF5BF6 AS DateTime))
INSERT [dbo].[Orders] ([or_id], [orName], [dateSta]) VALUES (4, N'费用', CAST(0x0000A03700FF5BF6 AS DateTime))
INSERT [dbo].[Orders] ([or_id], [orName], [dateSta]) VALUES (5, N'http://naoqiu.com', CAST(0x0000A03700FF5BF6 AS DateTime))
SET IDENTITY_INSERT [dbo].[Orders] OFF
/****** Object:  Default [DF_Orders_dateSta]    ｓｃｒｉｐｔ Date: 04/18/2012 15:31:11 ******/
ALTER TABLE [dbo].[Orders] ADD  CONSTRAINT [DF_Orders_dateSta]  DEFAULT (getdate()) FOR [dateSta]
GO

       现在我们先看在sql 2005上传统的分页存储过程：

Create Procedure [dbo].[prPageList]
(
    @PageSize int=20,/*每页行数*/
    @PageIndex int=1,/*传进页数*/    
    @Field nvarchar(2000),/*查询字段*/
    @QueryString nvarchar(3000),/*查询语句*/
    @Orderby nvarchar(1000)/*排序字段*/
)
AS
Set NoCount ON
  Declare @list_id1 int,@list_id2 int
  DECLARE @SqlQuery nvarchar(4000)
   Set @list_id2=(@PageSize*@PageIndex)--当前页记录结束
   Set @list_id1=(@list_id2-@PageSize)--当前页记录开始
 
  Set @SqlQuery=N'Select * From (Select ROW_NUMBER() Over(order by '
      +@Orderby
      +N')AS list_id,'
      +@Field
      +' '
      +@QueryString
      +N')AS A Where A.list_id>'
      +Cast(@list_id1 AS Nvarchar)
      +N' and A.list_id<='
      +Cast(@list_id2 AS Nvarchar)       
       print @SqlQuery
    Execute sp_executesql @SqlQuery
    
    Set @SqlQuery='Select count(*) '+@QueryString
    Execute sp_executesql @SqlQuery
    RETURN

　　调用示例：exec prPageList 10,100,'orderID','from orders','dateSta desc'

　　这种的编写有以下优点：