一个通用的分页存储过程实现-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 Util
GO
--创建通用的分页存储过程 USE Util GO /****** Object: StoredProcedure [dbo].[UP_GeneralPagedQuery_v1] Script Date: 04/03/2014 17:32:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- 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 Test GO --创建测试表 USE [Test] CREATE TABLE TableTest (ID INT,NAME NVARCHAR(50),CreateDate DATETIME) GO --插入测试数据 INSERT INTO TableTest SELECT 1,dean1,GETDATE() UNION SELECT 2,dean2,GETDATE() UNION SELECT 3,dean3,GETDATE() UNION SELECT 4,dean4,GETDATE()

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

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

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

 测试

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

DECLARE    @TotalCount int
EXEC Util.[dbo].[UP_GeneralPagedQuery_v1]
        @TableName = NTest.dbo.TableTest,
        @WhereField = NID > 0 AND ID < 100,
        @SelectField = NID,NAME,CreateDate,
        @OrderField = NNAME ASC,CreateDate DESC,
        @PageSize = 2,
        @PageIndex = 2,
        @TotalCount = @TotalCount OUTPUT

SELECT    @TotalCount as N@TotalCount

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

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。