sql 存储过程分页

sql 存储过程分页

来源： 作者： 2006-04-14 出处：pcdog.com

    CREATE PROC myx_prPageRecordset
    @queryStr nvarchar(1000),
    @keyField nvarchar (200),
    @pageSize int,
    @pageNumber int
    AS

    BEGIN
    DECLARE @sqlText AS nvarchar(4000)
    DECLARE @sqlTable AS nvarchar(4000)
    SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @queryStr
    SET @sqlText =
    'SELECT TOP ' + CAST(@pageSize AS varchar(30)) + ' * ' +
    'FROM (' + @sqlTable + ') AS tableA ' +
    'WHERE ' + @keyField + ' NOT IN(SELECT TOP ' +
    CAST(@pageNumber * @pageSize AS varchar(30)) + ' ' + @keyField +
    ' FROM (' + @sqlTable + ') AS tableB)'
    EXEC (@sqlText)
    END