sql-server - 遍历 SQL 表值参数

Question

我正在尝试为我的应用程序实现高级搜索功能，其中我有一个具有以下结构的 SQL 表值参数，

ColumnName   Operator       Keyword
------------------------------------
Name          StartsWith     Ram
City          Equals         Chennai

我的SQL 表，

Name         City      CreatedDate
-----------------------------------
Ram          Chennai   10/10/2014
Ramachan     Kovai     02/03/2015

我如何遍历此 TVP 以便我可以构建 WHERE 子句并将其附加到更快的 SELECT 查询因为我有大约 10 行搜索值(条件)。

过滤器与 AND 运算符相关联。

使用的运算符列表:

1. 等于
2. 不等于
3. 开始于
4. 结束于
5. 从(日期)
6. 到(日期)

Answer 1

您可以创建如下所示的动态过滤表达式，并在您的 SQL 中使用它。在您的 TVP 中添加编辑过滤器并根据各自的数据类型对其进行验证时，您需要非常小心

使用数据创建类型和基表

/*
CREATE TYPE FilterTVP AS TABLE
(
ColumnName VARCHAR(30), Operator VARCHAR(30), Keyword VARCHAR(30)
);
GO

CREATE TABLE myTable
(
    Name VARCHAR(50),
    City VARCHAR(50),
    CreatedDate DATE
)
INSERT INTO myTable VALUES('Ram','Chennai','10/10/2014'),('Ramachan','Kovai','02/03/2015')

*/

查询

DECLARE @Param FilterTVP
INSERT INTO @Param VALUES('Name','StartsWith','Ram'),('City','Equals','Chennai'),('CreatedDate','From','2014-05-05')
DECLARE @FilterExp NVARCHAR(MAX) 
SELECT @FilterExp = 
(SELECT
    ' AND ' + QUOTENAME(ColumnName,'[') + ' ' + 
    CASE Operator 
        WHEN 'Equals'
        THEN '='
        WHEN 'Not equals'
        THEN '<>'
        WHEN 'StartsWith'
        THEN 'LIKE'
        WHEN 'Endswith'
        THEN 'LIKE'
        WHEN 'From'
        THEN '>='
        WHEN 'To'
        THEN '<='

    END + ' ' +
    CASE
        WHEN Operator = 'Startswith' THEN  QUOTENAME(Keyword  + '%','''') 
        WHEN Operator = 'Endswith' THEN QUOTENAME('%' + Keyword  ,'''') 
        ELSE QUOTENAME(Keyword,'''')
    END
FROM @Param
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')



SET @FilterExp = 'SELECT * FROM  myTable WHERE 1=1 ' + ISNULL(@FilterExp,'')

PRINT @FilterExp
EXEC sp_executeSQL @FilterExp

输出

SQL Fiddle

Name    City    CreatedDate
--------------------------
Ram Chennai 2014-10-10