分享一个我自己写的支持多条件组合查询的分页存储过程
首先介绍一下该存储过程基于的一些条件:
1. MS SQL SERVER
2. 每个表需要有一个主键,名称为EntityId (当然你可以修改名称,只要你理解了该存储过程的实现)
实现方面的考虑:
1. 因为要支持多条件组合查询并且要有足够安全,所以必须使用sp_executesql这个以参数化执行的系统存储过程,否则只能用穷举法;
2. 性能方面:个人觉得目前有两种分页算法比较通用并且性能还可以,1)select top 颠颠倒倒法,但最后一页有bug; 2) ROW_NUMBER() 函数(仅SQL 2005支持),我选择了第一种。
因为最近写了几个通过这种方法实现的存储过程,发现了一些动态拼接参数化SQL的规律,觉得有一定价值,所以想拿出来分享给各位兄弟。
下面介绍我的实现方案:
1. 写一个最底层的分页存储过程sp_GetEntities,该存储过程不面向任何业务逻辑,只面向分页的实现逻辑,内部通过颠颠倒倒法实现分页,定义如下:
2. 写一个面向业务的分页存储过程,该存储过程接收一些与业务逻辑相关的参数,内部调用核心存储过程sp_GetEntities
下面举一个简单的例子说明如何设计表和第二个存储过程:
创建表(脚本基于SQL2005):
面向业务的分页存储过程:
调用该存储过程的示例代码:
在该例子中最终拼接出来的参数化SQL:
下面进行分析:
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. 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 OUTPUT AS DECLARE @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 SQL set @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 SQL set @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 count set @totalSQL = @sqlBegin + @VariableDecalrations + N' ' + @sqlPart1 + N';set @totalCount = @TotalRecords' -- Execute the SQL EXECUTE sp_executesql @totalSQL, N'@totalCount INT OUTPUT', @TotalRecords OUTPUT -- Connect the SQL to get the current page records set @totalSQL = @sqlBegin2 + @VariableDecalrations + N' ' + @sqlPart2 + N' ' + @sqlPart3 + N' ' + @sqlEnd -- Execute the SQL EXECUTE sp_executesql @totalSQL
2. 写一个面向业务的分页存储过程,该存储过程接收一些与业务逻辑相关的参数,内部调用核心存储过程sp_GetEntities
下面举一个简单的例子说明如何设计表和第二个存储过程:
创建表(脚本基于SQL2005):
- SQL code
- SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE 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] GO SET ANSI_PADDING OFF
面向业务的分页存储过程:
- SQL code
- CREATE PROCEDURE [dbo].[sp_GetPageDataFromSampleTable] @c1 int, @c2 int, @PageIndex int, @PageSize int, @TotalRecords int output AS 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) -- 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'' -- @c1 if not @c1 is null and @c1 > 0 begin set @variableDeclarations = @variableDeclarations + N'DECLARE @c1 int; set @c1 = ' + CAST(@c1 AS NVARCHAR(32)) + N';' set @dynamicConditions = @dynamicConditions + N' AND t.c1 >= @c1' set @parameterString1 = @parameterString1 + N'@c1 int, ' set @parameterString2 = @parameterString2 + N'@c1, ' set @parameterString3 = @parameterString3 + N',@c1 int' set @parameterString4 = @parameterString4 + N',@c1' end -- @c2 if not @c2 is null and @c2 > 0 begin set @variableDeclarations = @variableDeclarations + N'DECLARE @c2 int; set @c2 = ' + CAST(@c2 AS NVARCHAR(32)) + N';' set @dynamicConditions = @dynamicConditions + N' AND t.c2 >= @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 ' + @orderFields set @selectCountSql = N'SELECT t.EntityId FROM tb_SampleTable t WHERE 1 = 1' + @dynamicConditions -- Format @parameterString3 and @parameterString4 if @parameterString3 <> N'' set @parameterString3 = N', N''' + substring(@parameterString3, 2, len(@parameterString3) - 1) + N''', ' if @parameterString4 <> 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 int exec sp_GetPageDataFromSampleTable 2, 200, 2, 2, @TotalCount output print @TotalCount
在该例子中最终拼接出来的参数化SQL:
- SQL code
- DECLARE @TotalRecords int; set @TotalRecords = 0; DECLARE @c1 int; set @c1 = 2; DECLARE @c2 int; set @c2 = 200; -- Get total count EXECUTE sp_executesql N' SELECT @TotalRecords = COUNT(*) FROM ( SELECT t.EntityId FROM tb_SampleTable t WHERE 1 = 1 AND t.c1 >= @c1 AND t.c2 >= @c2 ) a', N'@c1 int, @c2 int, @TotalRecords INT OUTPUT', @c1, @c2, @TotalRecords OUTPUT; set @totalCount = @TotalRecords -- Get current page records SET 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 >= @c1 AND t.c2 >= @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 DESC EXECUTE 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
好了,差不多就这样吧。一切都是为了分享,为了大家一起进步。我有好的东西就会告诉大家。