create PROC P_GET_PAGE
(
@p_page int,
@p_page_size int
)
as
begin
set nocount on
declare @p_total_rows_num int
declare @p_first_selecting_row_num int
declare @p_first_selecting_row_id int
select @p_total_rows_num = count(IDFIELD) from TableName
select @p_first_selecting_row_num = (@p_page - 1) * @p_page_size + 1
if (@p_first_selecting_row_num <= @p_total_rows_num)
begin
set rowcount @p_first_selecting_row_num
select @p_first_selecting_row_id = IDFIELD
from TableName
order by 1
set rowcount @p_page_size
select * from TableName
where IDFIELD >= @p_first_selecting_row_id
order by 1
end
set nocount off
end
/* exe SP numpage,numitems*/
exec P_GET_PAGE 1, 4
29/07/08
Paging Using SQL Server SP
Subscrever:
Enviar feedback (Atom)
1 comentário:
IN SQL 2005 is a lot more simple
DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;
WITH OrdersRN AS
(
SELECT ROW_NUMBER() OVER(ORDER BY IDFIELD) AS RowNum
,*
FROM TBLNAME
)
SELECT *
FROM TBLNAME
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
ORDER BY IDFIELD;
Enviar um comentário