declare @PageSize int
declare @PageNumber int
declare @RowCount int
set @PageSize=20
set @PageNumber =1
set @RowCount =0
--根據頁碼和每頁大小計算起始行
declare @StartRowIndex int
if @PageSize < 1
set @StartRowIndex = 1
set @StartRowIndex = ((@PageNumber-1)*@PageSize+1)
DECLARE @PK nvarchar(50)
DECLARE @tblPK TABLE (
PK nvarchar(50) NOT NULL PRIMARY KEY
)
--聲明一個讀取主鍵的游標，讀取方式為：dynamic、read_only
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
-- 這裡只讀取主鍵，並插入表裡
select BasicInfoID from Employee_BasicInfo order by EmployeeName asc
--打開游標
OPEN PagingCursor
--直接跳到起始行
FETCH RELATIVE @StartRowIndex FROM PagingCursor INTO @PK
--不返回統計的行數
SET NOCOUNT ON
--開始循環讀取記錄
WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT @tblPK (PK) VALUES (@PK)
FETCH NEXT FROM PagingCursor INTO @PK
SET @PageSize = @PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
--查詢數據集合
SELECT BasicInfoID,EmployeeID,EmployeeName,EmployeeNumber
FROM dbo.Employee_BasicInfo A
JOIN @tblPK tblPK ON A.BasicInfoID = tblPK.PK
order by A.EmployeeName asc
