高性能SQLServer通用分页存储过程

最新推荐文章于 2024-09-17 13:42:29 发布

waiwai1015

最新推荐文章于 2024-09-17 13:42:29 发布

阅读量620

点赞数

文章标签： sqlserver 存储 null 数据库

本文链接：https://blog.csdn.net/waiwai1015/article/details/5843664

版权

   USE [ 数据库名称 ]
   GO
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
ALTER PROCEDURE [ dbo ] . [ dbTab_PagerHelper ]
     @TableName VARCHAR ( 50 ), -- 表名
     @FieldNames VARCHAR ( 1000 ), -- 显示列名，如果是全部字段则为*
     @WhereString VARCHAR ( 256 ) = NULL , -- 查询条件不含'WHERE'字符，如[id]>5 AND [userid]>10000
     @OrderField VARCHAR ( 256 ) = NULL , -- 排序不含'ORDER BY'字符，当@SortType=3时生效，必须指定ASC或DESC，建议在最后加上主键
     @OrderType TINYINT , -- 排序规则（1:单列正序ASC；2:单列倒序DESC；3:多列排序；）
     @PageIndex INT , -- 当前页数
     @PageSize INT , -- 每页输出的记录数
     @RecorderCount INT = 0 -- 记录总数，如果小于等于0则重新统计总数
AS
BEGIN
     SET NOCOUNT ON ;
     DECLARE @LowerBound int , @UpperBound int ;
     SET @LowerBound = ( @PageSize * ( @PageIndex - 1 ));
     SET @UpperBound = ( @LowerBound + @PageSize - 1 );
     DECLARE @MSSQL NVARCHAR ( 3000 ), @Where NVARCHAR ( 500 ), @Order VARCHAR ( 256 ), @Order2 VARCHAR ( 256 );
     SET @MSSQL = '' ;
     SET @Where = '' ;
     SET @Order = '' ;
     SET @Order2 = '' ;
     IF (( @WhereString IS NOT NULL ) AND ( @WhereString != '' ))
         SET @Where = ' WHERE ' + @WhereString ;
     IF (( @OrderType IS NOT NULL ) AND ( @OrderType > 0 ))
     BEGIN
         SET @Order = ' ORDER BY ' + @OrderField ;
         IF ( @OrderType = 1 )
             SET @Order = @Order + ' ASC ' ;
         ELSE IF ( @OrderType = 2 )
             SET @Order = @Order + ' DESC ' ;
         SET @Order2 = REPLACE ( REPLACE ( UPPER ( @Order ), ' ASC ' , ' {ASC} ' ), ' DESC ' , ' {DESC} ' );
         SET @Order2 = REPLACE ( REPLACE ( UPPER ( @Order2 ), ' {ASC} ' , ' DESC ' ), ' {DESC} ' , ' ASC ' );
     END
     -- 重新统计总记录数
     IF ( @RecorderCount <= 0 )
         EXECUTE ( ' SELECT COUNT(*) AS [RecorderCount] FROM ' + @TableName + @Where );
     ELSE
         SELECT @RecorderCount AS [ RecorderCount ] ;

     IF ( @PageIndex <= 1 ) -- 如果是第一页
     BEGIN
         SET @MSSQL = ' SELECT TOP ' + STR ( @PageSize ) + ' ' + @FieldNames + ' FROM ' + @TableName + @Where + @Order ;
     END
     ELSE IF (( @PageSize * @PageIndex ) >= @RecorderCount ) -- 如果是最后一页
     BEGIN
         SET @MSSQL = ' SELECT ' + @FieldNames + ' FROM (SELECT TOP ' + STR ( @PageSize - (( @PageSize * @PageIndex ) - @RecorderCount )) + ' ' + @FieldNames + ' FROM ' + @TableName + @Where + @Order2 + ' ) AS [PagerTempTable] ' + @Order ;
     END
     ELSE
     BEGIN
         IF ( @OrderType < 3 ) -- 单列排序分页方法
         BEGIN
             SET @MSSQL = ' SELECT TOP ' + STR ( @PageSize );
             SET @MSSQL = @MSSQL + ' ' + @FieldNames ;
             SET @MSSQL = @MSSQL + ' FROM ' + @TableName ;
             SET @MSSQL = @MSSQL + @Where ;
             DECLARE @TempStrings NVARCHAR ( 500 );
             SET @TempStrings = '' ;
             IF ( @OrderType > 0 )
             BEGIN
                 IF ( @Where <> '' )
                     SET @TempStrings = @TempStrings + ' AND ' ;
                 ELSE
                     SET @TempStrings = ' WHERE ' ;
                 SET @TempStrings = @TempStrings + ' ' + @OrderField ;

                 IF ( @OrderType = 1 )
                 BEGIN
                     SET @TempStrings = @TempStrings + ' > (SELECT MAX ' ;
                 END
                 ELSE
                 BEGIN
                     SET @TempStrings = @TempStrings + ' < (SELECT MIN ' ;
                 END
                 SET @TempStrings = @TempStrings + ' ( ' + @OrderField + ' ) FROM (SELECT TOP ' + STR ( @LowerBound ) + ' ' + @OrderField + ' FROM ' + @TableName + @Where + @Order ;
                 SET @TempStrings = @TempStrings + ' ) AS [PagerTempTable]) ' ;
             END
             SET @MSSQL = @MSSQL + @TempStrings + @Order ;
         END
         ELSE -- 多列排序分页方法
         BEGIN
             SET @MSSQL = ' SELECT TOP ' + STR ( @PageSize ) + ' ' + @FieldNames + ' FROM (SELECT TOP ' + STR ( @PageSize ) + ' ' + @FieldNames + ' FROM (SELECT TOP ' + STR ( @PageSize * @PageIndex ) + ' ' + @FieldNames + ' FROM ' + @TableName + @Where + @Order + ' ) AS [PagerTempTable] ' + @Order2 + ' ) AS [PagerTempTable] ' + @Order + ' ; '
         END
     END
     EXECUTE ( @MSSQL );
END