分享一个小弟我自己写的支持多条件组合查询的分页存储过程

分享一个我自己写的支持多条件组合查询的分页存储过程
首先介绍一下该存储过程基于的一些条件：
1. MS SQL SERVER
2. 每个表需要有一个主键，名称为EntityId (当然你可以修改名称，只要你理解了该存储过程的实现)

实现方面的考虑：
1. 因为要支持多条件组合查询并且要有足够安全，所以必须使用sp_executesql这个以参数化执行的系统存储过程，否则只能用穷举法；
2. 性能方面：个人觉得目前有两种分页算法比较通用并且性能还可以，1)select top 颠颠倒倒法，但最后一页有bug； 2) ROW_NUMBER() 函数（仅SQL 2005支持），我选择了第一种。

因为最近写了几个通过这种方法实现的存储过程，发现了一些动态拼接参数化SQL的规律，觉得有一定价值，所以想拿出来分享给各位兄弟。

下面介绍我的实现方案：
1. 写一个最底层的分页存储过程sp_GetEntities，该存储过程不面向任何业务逻辑，只面向分页的实现逻辑，内部通过颠颠倒倒法实现分页，定义如下：

SQL code

CREATE PROCEDURE [dbo].[sp_GetEntities]    @TableName NVARCHAR(128),    @ReturnFields NVARCHAR(2000),    @TopCount int,    @SqlFullPopulate NVARCHAR(4000),    @SqlPopulate NVARCHAR(4000),    @VariableDecalrations NVARCHAR(1024),    @ParameterString1 NVARCHAR(512),    @ParameterString2 NVARCHAR(512),    @ParameterString3 NVARCHAR(512),    @ParameterString4 NVARCHAR(512),    @DisOrderSql NVARCHAR(256),    @TotalRecords int OUTPUTASDECLARE @totalSQL NVARCHAR(4000)DECLARE @sqlBegin NVARCHAR(1024)DECLARE @sqlBegin2 NVARCHAR(1024)DECLARE @sqlPart1 NVARCHAR(4000)DECLARE @sqlPart2 NVARCHAR(4000)DECLARE @sqlPart3 NVARCHAR(4000)DECLARE @sqlEnd NVARCHAR(1024)-- Set the begin SQLset @sqlBegin = N'DECLARE @TotalRecords int;set @TotalRecords = 0;'set @sqlBegin2 = N'SET NOCOUNT ON;CREATE TABLE #t(IndexId INT IDENTITY (1, 1) NOT NULL,EntityId INT);CREATE TABLE #EntityIdTable(EntityId INT);'-- Set the end SQLset @sqlEnd = N'DROP TABLE #t;DROP TABLE #EntityIdTable;SET NOCOUNT OFF'-- Set the SQL to get the total records count.set @sqlPart1 = N'EXECUTE sp_executesql N''SELECT @TotalRecords = COUNT(*) FROM (' + @SqlFullPopulate + N') a''' + ', N''' + @ParameterString1 + N'@TotalRecords INT OUTPUT'', ' + @ParameterString2 + N'@TotalRecords OUTPUT'-- Set the SQL to get the record keys of the current page.set @sqlPart2 = N'EXECUTE sp_executesql N''' + N'INSERT INTO #t(EntityId) SELECT TOP ' + CAST(@TopCount AS NVARCHAR(20)) + N' EntityId FROM (' + @SqlPopulate + N') t ORDER BY ' + @DisOrderSql + N'''' + @ParameterString3 + @ParameterString4 + N'; INSERT INTO #EntityIdTable SELECT EntityId FROM #t ORDER BY IndexId DESC'-- Set the SQL to get the record entities of the current page.set @sqlPart3 = N'EXECUTE sp_executesql N''SELECT ' + @ReturnFields + N' FROM #EntityIdTable dt INNER JOIN [' + @TableName + N'] t ON dt.EntityId = t.EntityId'''-- Connect the SQL to get the total countset @totalSQL = @sqlBegin + @VariableDecalrations+ N''+ @sqlPart1+ N';set @totalCount = @TotalRecords'-- Execute the SQLEXECUTE sp_executesql @totalSQL, N'@totalCount INT OUTPUT', @TotalRecords OUTPUT-- Connect the SQL to get the current page recordsset @totalSQL = @sqlBegin2 + @VariableDecalrations+ N''+ @sqlPart2+ N''+ @sqlPart3+ N''+ @sqlEnd-- Execute the SQLEXECUTE sp_executesql @totalSQL

2. 写一个面向业务的分页存储过程，该存储过程接收一些与业务逻辑相关的参数，内部调用核心存储过程sp_GetEntities

下面举一个简单的例子说明如何设计表和第二个存储过程：
创建表（脚本基于SQL2005）：

SQL code

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[tb_SampleTable](    [EntityId] [int] IDENTITY(1,1) NOT NULL,    [c1] [int] NOT NULL,    [c2] [int] NULL,    [c3] [varchar](50) NULL, CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED (    [EntityId] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFF

面向业务的分页存储过程：

SQL code

CREATE PROCEDURE [dbo].[sp_GetPageDataFromSampleTable]    @c1 int,    @c2 int,    @PageIndex int,    @PageSize int,    @TotalRecords int outputASDECLARE @tableName NVARCHAR(128)DECLARE @topCount intDECLARE @returnFields NVARCHAR(2048)DECLARE @selectTopFields NVARCHAR(256)DECLARE @orderFields NVARCHAR(256)DECLARE @disOrderFields NVARCHAR(256)declare @variableDeclarations NVARCHAR(2000)DECLARE @dynamicConditions NVARCHAR(2048)DECLARE @parameterString1 NVARCHAR(2000)DECLARE @parameterString2 NVARCHAR(2000)DECLARE @parameterString3 NVARCHAR(2000)DECLARE @parameterString4 NVARCHAR(2000)DECLARE @selectCountSql NVARCHAR(1024) DECLARE @selectSql NVARCHAR(1024)-- begin to set the variable values according to the business logic.-- set @tableName.set @tableName = N'tb_SampleTable'-- set @topCount.set @topCount = (@pageIndex + 1) * @pageSize-- set @returnFields.set @returnFields = N't.EntityId,t.c1,t.c2,t.c3'-- set @selectTopFields. -- Notes: The select top fields must include the entityId and the order fields.set @selectTopFields = N't.EntityId,t.c3'-- set @orderFields and @disOrderFields.set @orderFields = N't.c3 asc'set @disOrderFields = N't.c3 desc'-- init the local variables.set @dynamicConditions = N''set @variableDeclarations = N''set @parameterString1 = N''set @parameterString2 = N''set @parameterString3 = N''set @parameterString4 = N''-- @c1if not @c1 is null and @c1 &gt; 0begin    set @variableDeclarations = @variableDeclarations + N'DECLARE @c1 int; set @c1 = ' + CAST(@c1 AS NVARCHAR(32)) + N';'    set @dynamicConditions = @dynamicConditions + N' AND t.c1 &gt;= @c1'    set @parameterString1 = @parameterString1 + N'@c1 int, '    set @parameterString2 = @parameterString2 + N'@c1, '    set @parameterString3 = @parameterString3 + N',@c1 int'    set @parameterString4 = @parameterString4 + N',@c1'end-- @c2if not @c2 is null and @c2 &gt; 0begin    set @variableDeclarations = @variableDeclarations + N'DECLARE @c2 int; set @c2 = ' + CAST(@c2 AS NVARCHAR(32)) + N';'    set @dynamicConditions = @dynamicConditions + N' AND t.c2 &gt;= @c2'    set @parameterString1 = @parameterString1 + N'@c2 int, '    set @parameterString2 = @parameterString2 + N'@c2, '    set @parameterString3 = @parameterString3 + N',@c2 int'    set @parameterString4 = @parameterString4 + N',@c2'end-- set @selectSql and @selectCountSql.set @selectSql = N'SELECT TOP ' + CAST(@topCount AS NVARCHAR(32)) + N' ' + @selectTopFields + N' FROM tb_SampleTable t WHERE 1 = 1' + @dynamicConditions + ' ORDER BY ' + @orderFieldsset @selectCountSql = N'SELECT t.EntityId FROM tb_SampleTable t WHERE 1 = 1' + @dynamicConditions-- Format @parameterString3 and @parameterString4if @parameterString3 &lt;&gt; N''    set @parameterString3 = N', N''' + substring(@parameterString3, 2, len(@parameterString3) - 1) + N''', 'if @parameterString4 &lt;&gt; N''    set @parameterString4 = substring(@parameterString4, 2, len(@parameterString4) - 1)-- Call sp_GetEntities to get the records of the current page and the total records count.exec sp_GetEntities@tableName,@returnFields,@PageSize,@selectCountSql,@selectSql,@variableDeclarations,@parameterString1,@parameterString2,@parameterString3,@parameterString4,@disOrderFields,@TotalRecords output

调用该存储过程的示例代码：

SQL code

declare @TotalCount intexec sp_GetPageDataFromSampleTable2,200,2,2,@TotalCount outputprint @TotalCount

在该例子中最终拼接出来的参数化SQL：

SQL code

DECLARE @TotalRecords int; set @TotalRecords = 0;DECLARE @c1 int; set @c1 = 2;DECLARE @c2 int; set @c2 = 200;-- Get total countEXECUTE sp_executesql N'SELECT @TotalRecords = COUNT(*) FROM (    SELECT t.EntityId FROM tb_SampleTable t WHERE 1 = 1 AND t.c1 &gt;= @c1 AND t.c2 &gt;= @c2) a',N'@c1 int, @c2 int, @TotalRecords INT OUTPUT',@c1, @c2, @TotalRecords OUTPUT;set @totalCount = @TotalRecords-- Get current page recordsSET NOCOUNT ON;CREATE TABLE #t(IndexId INT IDENTITY (1, 1) NOT NULL,EntityId INT);CREATE TABLE #EntityIdTable(EntityId INT);DECLARE @c1 int; set @c1 = 2;DECLARE @c2 int; set @c2 = 200;EXECUTE sp_executesql N'INSERT INTO #t(EntityId) SELECT TOP 2 EntityId FROM (    SELECT TOP 6 t.EntityId,t.c3 FROM tb_SampleTable t WHERE 1 = 1 AND t.c1 &gt;= @c1 AND t.c2 &gt;= @c2 ORDER BY t.c3 asc) t ORDER BY t.c3 desc', N'@c1 int,@c2 int', @c1,@c2;INSERT INTO #EntityIdTable SELECT EntityId FROM #t ORDER BY IndexId DESCEXECUTE sp_executesql N'SELECT t.EntityId,t.c1,t.c2,t.c3 FROM #EntityIdTable dt INNER JOIN [tb_SampleTable] t ON dt.EntityId = t.EntityId'DROP TABLE #t;DROP TABLE #EntityIdTable;SET NOCOUNT OFF

下面进行分析：
1. 相信大家已经看出来，我并没有一次性使用三次排序和TOP，而只使只使用两次，然后先把当前页的所有主键放入临时表。
然后再和你要查询的表关联。当初主要的考虑是这样清楚，大家有兴趣的可以直接用三次排序和TOP返回数据。

2. 刚刚直到写这个例子的时候才发现原来有个地方有问题：
DECLARE @c1 int; set @c1 = 2;
DECLARE @c2 int; set @c2 = 200;
这两句话重复定义和赋值了一遍。我回头修改下。不过似乎这样也没错，呵呵。

3. 所谓的拼接参数化SQL时的一些规律就是下面定义的一些变量：
DECLARE @tableName NVARCHAR(128)
DECLARE @topCount int
DECLARE @returnFields NVARCHAR(2048)
DECLARE @selectTopFields NVARCHAR(256)
DECLARE @orderFields NVARCHAR(256)
DECLARE @disOrderFields NVARCHAR(256)
declare @variableDeclarations NVARCHAR(2000)
DECLARE @dynamicConditions NVARCHAR(2048)
DECLARE @parameterString1 NVARCHAR(2000)
DECLARE @parameterString2 NVARCHAR(2000)
DECLARE @parameterString3 NVARCHAR(2000)
DECLARE @parameterString4 NVARCHAR(2000)
DECLARE @selectCountSql NVARCHAR(1024)
DECLARE @selectSql NVARCHAR(1024)

只要按照你自己的业务逻辑，正确对这些变量进行赋值，那就可以直接调用核心的分页存储过程了。
@selectCountSql表示定义一个可以查询出根据所有条件可以获取总返回记录的SQL；
@selectSql表示定义一个可以查询出返回TOP @topCount的SQL；

DECLARE @parameterString1 NVARCHAR(2000)
DECLARE @parameterString2 NVARCHAR(2000)
DECLARE @parameterString3 NVARCHAR(2000)
DECLARE @parameterString4 NVARCHAR(2000)
这四个变量纯粹是为了提供格式化参数给sp_executesql

好了，差不多就这样吧。一切都是为了分享，为了大家一起进步。我有好的东西就会告诉大家。

[解决办法]
1，我从sql6.5用到2008，比较下来三次top还是比较简单的，我又加了折半方式(可支持千万级的数据分页)
2，还有一种Set Rowcount @number 方式，在大数据量(>500万)的分页下，比三次top效果好
3，LZ的这个可能效率还是可以的，但是对tempdb库，IO有一定的影响；还有就是SP的编译缓存方面也会有一定的影响
4，从2005以后支持top后加参数，这个应该会对sp的编译有很大帮助
[解决办法]

探讨
引用:
支持

谢谢各位红星星的甚至是钻石级别的兄弟支持我，你们的支持是对我莫大的鼓励，哈哈。

分享一个小弟我自己写的支持多条件组合

热点推荐