T-SQL分页功能存储过程

分页功能存储过程

ALTER PROCEDURE [dbo].[P_SplitPagesQuery]

@TablesName NVARCHAR(MAX),--表名或视图名（只能传单一表名）

@PK NVARCHAR(MAX)='',--主键（可以为空！）

@StrSort NVARCHAR(MAX)='',--排序字段（按照这个字段对查询结果进行排序），不能为空

@SortType INT =0,--排序方式，默认为0（升序），1为降序

@StrWhere NVARCHAR(MAX)='',--查询条件（即sql语句中的where后面部分）

@Fields NVARCHAR(MAX)='*',--读取字段（呈现出来的字段）

@PageCurrent INT =1,--当前页码，默认为1

@PageSize INT =10,--页面大小，默认为10

@PageCount INT OUTPUT,--总页数（按照页面大小一共分了多少页）

@RecordCount INT OUTPUT--查询结果的总条数

AS

/*

    返回值说明:查询结果(表)

*/

DECLARE @SqlStr nvarchar(4000)

IF @StrWhere IS NOT NULL AND @StrWhere != ''

   BEGIN

     SET @StrWhere = ' WHERE ' + @StrWhere + ' '

   END

ELSE

   BEGIN

      SET @StrWhere = ''

   END

BEGIN

   IF @StrSort = ''

      IF @PK  IS NOT NULL AND @PK!=''

                SET @StrSort = @PK

   IF @PageCurrent < 1

      SET @PageCurrent = 1

     IF @SortType = 1 AND @StrSort!=''

            SET @StrSort = @StrSort + ' DESC '

   IF @SortType = 0 AND @StrSort!=''

            SET @StrSort = @StrSort + ' ASC '

   IF @PageCurrent = 1 --第一页提高性能

       BEGIN

           SET @SqlStr = 'SELECT TOP ' + str(@PageSize) +' '+@Fields+ ' FROM ' + @TablesName +

              ' AS tb ' + @StrWhere

                     IF @StrSort!=''

                            SET @SqlStr = @SqlStr + ' ORDER BY '+ @StrSort

       END

   ELSE

      BEGIN

         DECLARE @StartPos NVARCHAR(50)

         DECLARE @EndPos NVARCHAR(50)

         SET @StartPos = CONVERT(NVARCHAR(50),(@PageCurrent - 1) * @PageSize + 1)

         SET @EndPos = CONVERT(NVARCHAR(50),@PageCurrent * @PageSize)

         SET @SqlStr =  ' SELECT '+@Fields+ '

         FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@StrSort+') AS RowNum , tb.* '+'

         FROM '+@TablesName+' AS tb '+@StrWhere+') AS D

         WHERE RowNum BETWEEN '+@StartPos+' AND ' +@EndPos

                 IF @StrSort!=''

                            SET @SqlStr = @SqlStr + ' ORDER BY '+ @StrSort

      END

END

print @SqlStr

EXEC(@SqlStr)

DECLARE @SqlCount NVARCHAR(4000)

SET @SqlCount = 'SELECT @RecordCount=COUNT(*),@PageCount=CEILING((COUNT(*)+0.0)/'

                    + CAST(@PageSize AS VARCHAR)+') FROM ' + @TablesName + @StrWhere

--输出参数赋值

EXEC SP_EXECUTESQL @SqlCount,N'@RecordCount INT OUTPUT,@PageCount INT OUTPUT',

                               @RecordCount OUTPUT,@PageCount OUTPUT

调用示例

EXEC @x =[dbo].[P_SplitPagesQuery] '表或视图','',@sOrderBy,@nOrderTpye,@sSqlString,'*',

        @nPageNum,@nPageSize,@PageCount OUTPUT,@RecordCount OUTPUT

......