一个支持多条件组合查询的分页存储过程

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

首先介绍一下该存储过程基于的一些条件：
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