分页查询的存储过程

1.id是数字型或有序的字符型

第一页、上一页、下一页都可用
select top n * from 表 where id>=参数值

最后一页
select @m=count(*) from 表
@m=n%@m
select top @m * from 表 into #tmp1 order by id desc
select * from #tmp1 order by id

或者
select * from 表 where id not in (select top x from 表)

2.
create proc GetAuthors
@Author_Last_Name as varchar(100) = null,
@StartRow as int = null,
@StopRow as int = null
AS

---- 建立有标识符列的table变量
declare @t_table table
(
[rownum] [int] IDENTITY (1, 1) Primary key NOT NULL ,
[Author_Last_Name] [varchar] (40) ,
[Author_First_Name] [varchar] (20) ,
[phone] [char] (12) ,
[address] [varchar] (40) ,
[city] [varchar] (20) ,
[state] [char] (2) ,
[zip] [char] (5)
)

---- 在返回指定的@StopRow行数之后停止处理查询
Set RowCount @StopRow

---- 插入到table变量中
insert @t_table
(
[Author_Last_Name],[Author_First_Name],[phone],[address],[city],[state],[zip]
)
SELECT [Author_Last_Name],[Author_First_Name],[phone],[address],[city],[state],[zip]

FROM authors
WHERE Author_Last_Name like '%' + @Author_Last_Name + '%'
ORDER BY Author_Last_Name

---- 返回到正确的结果
SELECT * FROM @t_table WHERE rownum >= @StartRow
ORDER BY rownum

GO