存储过程分页 Java调用_存储过程分页又一方法（使用table变量）

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