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
好了，差不多就这样吧。一切都是为了分享，为了大家一起进步。我有好的东西就会告诉大家。
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
