SET QUOTED_IDENTIFIER ON
GO
SET
ANSI_NULLS ON
GO
ALTER
PROCEDURE dbo.spSourcePage
@WhereKey VARCHAR(2000),
@SortKey VARCHAR(100),
@SortFun INT,
@PageIndex INT,
@PageSize INT
--@RowCount INT OUTPUT
AS
BEGIN
DECLARE
@StartRowIndex INT;
DECLARE
@Sort VARCHAR(10);
DECLARE
@tsql NVARCHAR(4000);
SET
@tsql = '';
IF
LEN(@WhereKey) = 0
BEGIN
SET @WhereKey = '1=1';
END
--
根据页码和每页大小计算起始行
IF
@PageSize < 1
SET @StartRowIndex = 1
SET
@StartRowIndex = ( (@PageIndex-1)* @PageSize + 1);
DECLARE
@PK VARCHAR(50);
DECLARE
@tblPK TABLE ( PK nvarchar(50) NOT NULL PRIMARY KEY );
IF
@SortFun = 0
BEGIN
SET @Sort = ' DESC';
END
ELSE
BEGIN
SET @Sort = ' ASC';
END
EXEC
('
--
声明一个读取主键的游标
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
--
这里只读取主键,并插入表里
SELECT source_id FROM Source WHERE ' + @WhereKey + ' ORDER BY ' + @SortKey + @Sort
)
--
打开游标
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 )
SET @tsql = ',' + @PK + @tsql
FETCH NEXT FROM PagingCursor INTO @PK
SET @PageSize = @PageSize - 1
END
CLOSE
PagingCursor
DEALLOCATE
PagingCursor
if LEN(@tsql) > 0
BEGIN
SET @tsql = RIGHT(@tsql,LEN(@tsql)-1);
EXEC('SELECT * FROM Source WHERE source_id in (' + @tsql+ ') ORDER BY ' + @SortKey + @Sort)
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS ON
GO