SQL - 按页检索数据 [英] SQL - Retrieve data pagewise

问题描述

我有一个存储过程，它返回一个联系人列表.由于有很多联系人，并且出于性能原因，我希望以页面方式检索联系人，即在每个实例中仅获取 20 个联系人.

I have a stored procedure which returns a list of contacts. Since there are many contacts, and for performance reasons, I wish to retrieve the contacts in a page-wise manner, whereby only 20 contacts are fetched in the each instance.

我的表单上有一个按钮可以让我进入下一页，一旦点击该按钮，我就会去获取接下来的 20 个联系人.

A button on my form will allow me to go to the next page, and once the button is clicked, I go and fetch the next 20 contacts.

这是我的存储过程；

CREATE PROCEDURE [dbo].[GetAllContacts] (@searchVal VARCHAR(500))
AS
  BEGIN
      SELECT DISTINCT ( Id ) AS Id,
                      FirstName,
                      LastName,
                      Address,
                      Tel_no
      FROM   tblContact
      WHERE  ( FirstName LIKE ( '%' + @searchVal + '%' )
                OR LastName LIKE ( '%' + @searchVal + '%' ) )
      ORDER  BY LastName
  END 

如何拆分查询以及如何检索第 2 页的下 20 个联系人和第 3 页的第 3 个 20 个联系人?

How do I split the query and how do I retrieve the next 20 contacts for the 2nd page and 3rd 20 contacts for the 3rd page?

我使用的是 MS SQL Server 2012.

I'm using MS SQL Server 2012.

推荐答案

我找到了(通过使用 orderedIndex )；

I found it (By using an orderedIndex );

ALTER PROCEDURE [dbo].[GetAllContacts] (@searchVal  varchar(500) 
                                     ,  @CurrentPage int
                                     ,  @PageSize   int)
AS
BEGIN

DECLARE @RESULTS TABLE (
        orderedIndex                    int IDENTITY(1,1) PRIMARY KEY
    ,   Id                              bigint        NOT NULL
    ,   FirstName                       nvarchar(30)  NULL
    ,   LastName                        nvarchar(30)  NULL
    ,   Address                         nvarchar(130)  NULL
    ,   Tel_no                          nvarchar(15)  NULL )

SET @CurrentPage = ISNULL(@CurrentPage, 1)
SET @PageSize = ISNULL(@PageSize, 10)


INSERT INTO @RESULTS (Id, FirstName, LastName, Address,Tel_no)    
Select  distinct(Id) as Id
  , FirstName
  , LastName
  , Address
  , Tel_no
from    tblContact     
Where (FirstName like ('%'+ @searchVal +'%') OR LastName  like ('%'+ @searchVal +'%'))
Order by LastName

-- Get result on separate pages
SELECT     Id
        ,  FirstName
        ,  LastName                 
        ,  Address
        ,  Tel_no
        , (SELECT COUNT(*) FROM @RESULTS) AS NbResults
        , @CurrentPage AS CurrentPage
        , @PageSize AS PageSize
        , FLOOR(CEILING(Cast((SELECT COUNT(*) FROM @RESULTS) as decimal(18,2))/ @PageSize)) as TotalPages 

FROM @RESULTS 
WHERE orderedIndex BETWEEN 1 + ((@CurrentPage - 1) * @PageSize) AND (@CurrentPage) * @PageSize

END

这篇关于SQL - 按页检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！