使用sql 2008进行分页 [英] paging with sql 2008

问题描述

我在SQL Server 2008中有这个查询r2

I have this query in SQL Server 2008 r2

SELECT Row, id, id_2, car_num, photo, doc_type
FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) AS Row, id, id_2, car_num, photo, doc_type
FROM Table_2 AS tbl) AS derivedtbl_1
WHERE (Row >= 1) AND (Row <= 5)

当我在vb net sqldataadapter中执行查询时，它获取了5条记录，但它没有移动到接下来的5条记录，它是一张带照片的详细信息表（将文件归档为扫描仪的jpg）id_2是FK，12之后记录我ge t'内存不足'，并且每次只想从Table_2加载5条记录。我想做分页。

when I execute the query in vb net sqldataadapter it gets the 5 records but it doesn't move to next 5 records, it is a detail table with photo (archiving of documents as jpg from scanner) id_2 is the FK, after 12 records I get 'out of memory' and want to load only 5 records from Table_2 each time. I want to do paging.

推荐答案

创建以下程序来实现分页：

Create below procedure to implement paging:

CREATE PROCEDURE dbo.proc_Paging_TempTable
(
@Page int,
@RecsPerPage int
)
AS

-- The number of rows affected by the different commands
-- does not interest the application, so turn NOCOUNT ON
SET NOCOUNT ON;

-- Determine the first record and last record 
DECLARE @FirstRec int, @LastRec int;

SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Return the set of paged records
WITH TempResult AS
(
SELECT ROW_NUMBER() OVER(ORDER BY id) AS RowNum, id, id_2, car_num, photo, doc_type
FROM Table_2
)

SELECT *
FROM TempResult
WHERE RowNum > @FirstRec 
AND RowNum < @LastRec;

GO

EXEC proc_Paging_TempTable 1, 5

您需要将页面号和页面号传递给存储过程。在前面的过程中，它将页面号传递为1，每页的记录数不是5.如果你想要下一个结果集，那么你需要传递2,5

You need to pass page no and no of page to stored procedure. In preceding procedure it is passing page no as 1 and no of records per page is 5. If you want next result set then you need to pass 2,5

这篇关于使用sql 2008进行分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！