SQL Server在where子句中包含搜索筛选器列表
我有一个SQL Server数据库,其中包含客户数据的Person
表。在我的Web应用程序中,弹出式搜索框(jqGrid)允许用户指定在搜索中使用的字段/过滤器以及操作和数据。所以这些字段就像名字,上次访问日期等(映射到Person
表,虽然不是所有的字段都与列名匹配),并且操作是包含,开始于,结束于,大于等等(每一个都被传递以“cn”,“bw”,“ew”,“gt”)这样的双字母代码表示。总共有15个不同的领域可以被过滤和12个操作。SQL Server在where子句中包含搜索筛选器列表
(有表中的约50列,所以我只是这里包括几个):
CREATE TABLE [dbo].[Person]
(
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[PersonTypeId] [int] NULL,
[Firstname] [nvarchar](200) NULL,
[Middlename] [nvarchar](200) NULL,
[Lastname] [nvarchar](200) NULL,
[Name] [nvarchar](200) NULL,
)
INSERT INTO [dbo].[Person]([PersonTypeId],[Firstname],[Middlename],[Lastname],[Name])
VALUES (1, 'James', 'Joseph', 'Martin', ''),
(1, 'Jim', 'Joseph', 'Martyn', ''),
(1, 'James', 'John', 'Martine', ''),
(2, 'James', 'Sean', 'Martin', 'Martin & Co'),
(2, 'John', 'Joseph', 'Martin', 'Martin & Martin')
我添加了一个表值参数的存储过程来传递效果很好的搜索条件,所以现在在搜索存储过程中,我有一个udt包含用户输入的所有字段/操作/数据。
CREATE TYPE [dbo].[udt_Filter] AS TABLE
(
[Field] [nvarchar](50),
[Op] [nvarchar](50),
[Data] [nvarchar](50)
)
通过UDT传递会是这样的过滤器:
Field Op Data
'Name', 'bw', 'Mart'
'LastVisit', 'gt', '01.07.2017'
现在,我不知道什么是包含在搜索过滤器的最佳途径。我知道我可以使用动态SQL进行工作,但如果可能的话,我宁愿使用连接来完成它,但是我看不到任何将Person名称过滤器与Person表中的数据结合在一起的方式。
ALTER PROCEDURE [dbo].[pr_GetPersons]
(
@Filters dbo.udt_Filter READONLY,
@Operation nvarchar(3) -- AND or OR
)
AS
BEGIN
SELECT [PersonTypeId], [Firstname], [Middlename], [Lastname], [Name]
FROM [dbo].[Person]
WHERE PersonTypeId IN (1, 2, 3)
-- @Operation can I somehow join to @Filters and filter the Select result using each row in @Filters??
END
所有过滤条件都与AND或OR结合,它们作为@Operation传递到存储过程。
不可能通过Person
表和@Filters
表参数之间的连接来解决此动态过滤。如果你没有动态SQL,有15个字段和12个操作符,你最终会得到一个怪物where子句。我怀疑存储过程在这种情况下会表现得很好。
相反,我建议去动态SQL。这里有一个存储过程可以用作起点:
CREATE PROCEDURE [dbo].[pr_GetPersons]
(@Filters dbo.udt_Filter READONLY)
AS
BEGIN
DECLARE
@SelectList nvarchar(max),
@WhereClause nvarchar(max),
@SqlCommand nvarchar(max)
SET @SelectList = ISNULL(STUFF((SELECT ', ' + QUOTENAME(Field) AS [text()] FROM @Filters FOR XML PATH('')), 1, 1, ''), '*')
SET @WhereClause = STUFF((
SELECT
' AND ' +
-- Translate opcodes into SQL Server expressions.
CASE Op
WHEN 'cn' THEN QUOTENAME(Field) + ' LIKE ' + QUOTENAME('%' + Data + '%', '''')
WHEN 'bw' THEN QUOTENAME(Field) + ' LIKE ' + QUOTENAME(Data + '%', '''')
WHEN 'ew' THEN QUOTENAME(Field) + ' LIKE ' + QUOTENAME('%' + Data, '''')
WHEN 'gt' THEN QUOTENAME(Field) + '>' + QUOTENAME(Data, '''')
WHEN 'lt' THEN QUOTENAME(Field) + '<' + QUOTENAME(Data, '''')
END AS [text()]
FROM
@Filters
FOR XML PATH(''), type).value('.', 'nvarchar(max)'),
1, 5, '')
SET @SqlCommand = 'SELECT ' + @SelectList + ' FROM dbo.Person WHERE PersonTypeId IN (1, 2, 3)' + ISNULL(NULLIF(' AND ', @WhereClause) + @WhereClause, '')
EXEC(@SqlCommand)
END
它只返回过滤器中的列。如果您希望它返回表格中的所有列,而不管过滤器中的内容如何,请将@SelectList
变量设置为*
。
所有过滤条件都使用AND运算符连接。 (在@Fields
变量没有迹象显示如何将它们结合起来。所以我想和运营商所需要的。)
的CASE
表达式解释来自@Filters
表操作码生成where子句可以移动到自己的功能。 QUOTENAME(Data, '''')
用于在这种情况下形成正确的字符串,当Data
是一个字符串并且包含单引号时。
该过程不检查Person
表中是否存在过滤器字段。应特别注意滤波器值(Data
),尤其是日期/时间和小数类型,因为没有类型转换(所有滤波器值在形成where子句时用作字符串),也不检查过滤器值类型是否与列匹配类型在Person
表中。
希望它有帮助。
过滤器是否总是与'和',''或'或用户指定的选项组合?一些建议的背景阅读是[这里](http://www.sommarskog.se/dyn-search-2008.html)。 – HABO
Hi @HABO - 我没有提到所有的陈述都是用AND或OR结合的,加上最初的WHERE PersonTypeId IN子句 –
你如何处理子表达式,例如: 'A和(B或C)而不是(D或(E而不是F))'?目前还不清楚你在'Field' /'Op' /'Data'中的表现方式,更不用说'和'/'或'了。 – HABO