一个通用的分页存储过程实现-SqlServer(附上sql源码,一键执行即刻搭建运行环境)

9/1/2015来源:SQL技巧人气:1599

一个通用的分页存储过程实现-SqlServer(附上sql源码,一键执行即刻搭建运行环境)

使用前提

  查询表必须有ID字段,且该字段不能重复,建议为自增主键

背景

  如果使用ADO.NET进行开发,在查询分页数据的时候一般都是使用分页存储过程来实现的,本文提供一种通用的分页存储过程,只需要传入:

  1. 表名(以DBName.dbo.TableName)的形式
  2. Where条件(ID > 0 AND ID < 100)
  3. Select字段(ID,NAME,CreateDate)
  4. Order字段(NAME ASC,CreateDate DESC)
  5. PageSize (15)
  6. PageIndex(2)
  7. TotalCount,此为output参数

  这7个参数,存储过程就能够返回指定条件下的分页数据,和数据总数。

sql源码&测试环境搭建

--创建Util库CREATE DATABASE UtilGO--创建通用的分页存储过程USE UtilGO/****** Object:  StoredPRocedure [dbo].[UP_GeneralPagedQuery_v1]    Script Date: 04/03/2014 17:32:07 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- Author:        DeanZhou-- Create date: 2013-09-24-- Description:    通用的分页存储过程(一)CREATE PROCEDURE [dbo].[UP_GeneralPagedQuery_v1]    @TableName VARCHAR(100) ,                    --表名称:如 MKT.dbo.UV_CouponInfo    @WhereField NVARCHAR(1000) = '' ,            --筛选条件:如 Status = 1 AND CreateUser = 'admin'    @SelectField NVARCHAR(1500) = '*' ,          --需要查询的列:如 *    @OrderField NVARCHAR(1000) = '' ,            --需要进行排序的字段:如  CustomerName desc,StartDate asc,Status desc,id asc     @PageSize INT = 15 ,                         --页面大小:如 15    @PageIndex INT = 1 ,                         --当前页面:如 1    @TotalCount INT = 0 OUT                      --记录总数:输出值AS     BEGIN           IF @OrderField IS NULL OR @OrderField = ''             BEGIN                SET @OrderField = ' ID '            END        IF @WhereField IS NULL OR @WhereField = ''             BEGIN                SET @WhereField = ' WHERE ID > 0 '            END        ELSE             BEGIN                SET @WhereField = ' WHERE ' + @WhereField            END                    DECLARE @ExceptCount INT = @PageSize * ( @PageIndex - 1 )        DECLARE @TakeCount INT = @PageSize            DECLARE @IsNeedSubQuery INT                DECLARE @SqlPreview NVARCHAR(MAX) =               'SELECT @C = COUNT(1) FROM ' + @TableName + ' ' + @WhereField + ';'            + 'IF @EC < 0 SET @EC = 0 IF @EC >= @C SET @EC = @C;'            + 'IF @TC < 0 SET @TC = 15 IF (@EC + @TC) > @C SET @TC = @C - @EC;'            + 'IF @EC > 0 AND @TC > 0 SET @NSQ = 1 ELSE SET @NSQ = 0;'                                                     EXEC sp_executesql @SqlPreview,            N'@C INT OUTPUT,@EC INT OUTPUT,@TC INT OUTPUT,@NSQ INT OUTPUT',            @TotalCount OUTPUT, @ExceptCount OUTPUT, @TakeCount OUTPUT, @IsNeedSubQuery OUTPUT                        DECLARE @MaxOrMin VARCHAR(3) = 'MAX'        DECLARE @DescOrAsc VARCHAR(4) = ''        IF @ExceptCount > @TotalCount / 2             BEGIN                SET @MaxOrMin = 'MIN'                SET @DescOrAsc = 'DESC'                SET @ExceptCount = @TotalCount - @ExceptCount + 1            END        DECLARE @SqlQuery NVARCHAR(MAX) =               ' DECLARE @T_IDS TABLE (ID INT) '                + ' IF @NSQ = 1' +                     ' BEGIN ' +                     '    SELECT @MD = ' + @MaxOrMin + '(ID) FROM '+                    '        (SELECT TOP ' + CONVERT(VARCHAR(15), @ExceptCount) + ' ID FROM ' + @TableName + ' ' + @WhereField + ' ORDER BY ' + @OrderField + ')T1;' +                     '    INSERT INTO @T_IDS '+                    '         SELECT TOP ' + CONVERT(VARCHAR(15), @TakeCount) + ' ID FROM ' + @TableName + ' ' + @WhereField + ' AND ID > @MD  ORDER BY ' + @OrderField +                     '    SELECT ' + @SelectField + ' FROM ' + @TableName + ' S WHERE ID IN (SELECT ID FROM @T_IDS) ORDER BY ' + @OrderField +                     ' END ' +                   ' ELSE' +                     ' BEGIN ' +                     '    INSERT INTO @T_IDS SELECT ID FROM (SELECT TOP ' + CONVERT(VARCHAR(15), @TakeCount) + ' ID FROM ' + @TableName + ' ' + @WhereField + ' ORDER BY  ' + @OrderField + ')T;' +                     '    SELECT ' + @SelectField + ' FROM ' + @TableName + ' S WHERE ID IN (SELECT ID FROM @T_IDS) ORDER BY ' + @OrderField +                     ' END '                                                  EXEC sp_executesql @SqlQuery, N'@MD INT,@NSQ INT', 0, @IsNeedSubQuery            END           GO    --创建测试库CREATE DATABASE TestGO--创建测试表USE [Test]CREATE TABLE TableTest (ID INT,NAME NVARCHAR(50),CreateDate DATETIME)GO--插入测试数据INSERT INTO TableTestSELECT 1,'dean1',GETDATE() UNIONSELECT 2,'dean2',GETDATE() UNIONSELECT 3,'dean3',GETDATE() UNIONSELECT 4,'dean4',GETDATE() 

  打开您的sqlserver,在本地新建查询,并运行上面的代码,会在你的数据库中创建以下内容:

  1. 一个名称为【Util】的数据库,该库下面有一个名为【UP_GeneralPagedQuery_v1】的存储过程,这个存储过程就是通用的分页存储过程。
  2. 一个名称为【Test】的数据库,该库下面有一个名为【TableTest】的表,这个表里面有4条数据

请注意:在执行sql之前确认一下没有重名数据库,以免出错

测试

  新建一个查询,执行下面sql,就完成了分页数据的获取

DECLARE    @TotalCount intEXEC Util.[dbo].[UP_GeneralPagedQuery_v1]        @TableName = N'Test.dbo.TableTest',        @WhereField = N'ID > 0 AND ID < 100',        @SelectField = N'ID,NAME,CreateDate',        @OrderField = N'NAME ASC,CreateDate DESC',        @PageSize = 2,        @PageIndex = 2,        @TotalCount = @TotalCount OUTPUTSELECT    @TotalCount as N'@TotalCount'