如何调用分页存储过程?
哪位大侠能给个例子啊 小弟感激不禁

[ 本帖最后由 wangnannan 于 2009-11-19 12:07 编辑 ]

创建存储过程：//这点很重要,这里使用的是table变量（SQL Server 2000)，你也可以使用临时表

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


下面是“下一页”调用的例子：（GRID如何连接数据库就不写了）

Private Sub ButtonNext_Click (ByVal sender As Object, _
ByVal e As System.EventArgs) Handles ButtonNext.Click

viewstate("StartRow") = viewstate("StartRow") + dgrid.PageSize
viewstate("StopRow") = viewstate("StartRow") + dgrid.PageSize

'运行存储过程，返回SQLDataReader,CreateFenYe你自已写，三个参数正是上面的存储过程需要的
dgrid.DataSource = CreateFenYe(textAu_lname.Text, _
textAu_fname.Text, viewstate("StartRow"),viewstate("StopRow"))
dgrid.DataBind()

End Sub