接上文：T-SQL动态查询（1）——简介

?

前言：

在开发功能的过程中，我们常常会遇到类似以下情景：应用程序有一个查询功能，允许用户在很多查询条件中选择所需条件。这个也是本系列的关注点。

但是有时候你也许会发现，有些条件或多或少是互相排斥的。比如用户通过下面其中一个条件查找信息：

1． 客户名

2． 客户ID

3． 客户身份标识号（如国内身份证、美国社保号等）。

并且这三列上都有适当的索引。本系列主要研究动态SQL和OPTION(RECOMPILE)查询提示来处理需求，但是前面已经提到过，频繁编译、重编译会给服务器带来严重压力，特别是对于那些操作特别频繁的功能。另外对于一些简单的问题使用动态SQL也略微浪费。

针对上面的问题，我们可以使用一个较为轻量的方法：使用IF语句。

IF语句：

针对上面问题，我们来看看下面例子：

?

IF @custno IS NOT NULL  --客户号
   SELECT ... FROM customers WHERE custno = @custno
ELSE IF @idno IS NOT NULL  --身份标识号
   SELECT ... FROM customers WHERE natregno = @natregno
ELSE IF @custname IS NOT NULL --客户名
   SELECT TOP 200 ...
   FROM   customers
   WHERE  custname LIKE @custname + '%'
   ORDER  BY custname
ELSE
   RAISERROR('没有提供查询条件！', 16, 1)

?

注意：不要过于纠结里面的表和列是否存在，这里只是个大概演示。

另外，上面的TOP 200是为了避免由于用户输入一个非常短的字符串查询客户名时，由于过于模糊的查询导致返回大量数据导致性能问题。

如果这时候你系统同时返回其他表的数据，并且不喜欢重复JOIN，可以把所有匹配的客户数预存到一个表变量或临时表，然后最后再JOIN：

?

IF @custno IS NOT NULL
   INSERT @cust(custno) VALUES (@custno)
ELSE IF @natregno IS NOT NULL
   INSERT @cust(custno) SELECT custno FROM customers WHERE natregno = @natregno
ELSE IF @custname IS NOT NULL
   INSERT @cust(custno)
      SELECT TOP (200) custno
      FROM   customers
      WHERE  custname LIKE @custname + '%'
      ORDER  BY custname
ELSE
   RAISERROR('没有提供查询条件！', 16, 1)
 
SELECT ...
FROM   @cust c
JOIN   customers cst ON cst.custno = c.custno
JOIN   ...

?

这种写法有一个潜在的性能问题，不管用户如何选择查询条件，我们都希望优化器能使用查询列上的索引。但是由于SQL Server创建执行计划的方式导致这种情况很难总是如愿。前文提到过参数嗅探的问题，当存储过程被执行并在缓存中没有找到可重用的执行计划时，SQL Server会对整个存储过程及当前值进行“嗅探”，产生一个对当前值最优的执行计划。

换句话说，如果第一个用户选择以客户号作为参数查询，那么优化器会对客户号进行优化，而底层处理中会对客户名附以NULL的形式，如果后续用户使用客户名进行搜索，会导致表扫描，这种情况肯定不是你期望的。

为了避免这种情况，可以使用一些预防措施。其中一种是把存储过程中原有的SELECT语句拆成三种针对性的SELECT语句，但是无可否认的是，这种方式会使得语句越来越庞大。另外一种是在语句中使用索引提示指定索引，但是这样语句就被绑死了，如果由于某些原因导致索引重命名，那么语句会运行失败。

在某种程度上，使用适当的OPTIMIZEFOR提示可能是更好的选择：

?

SELECT TOP 200 custno
FROM   customers
WHERE  custname LIKE @custname + '%'
ORDER  BY custname
OPTION (OPTIMIZE FOR (@custname = N'ZZZZZZZ'))

?

这种提示会触发SQL Server针对你指定的值（如上面的ZZZZZZ）进行查询计划的创建，而不会在乎你实际传入什么值。但是这时你就要挑选一个有足够选择度和代表性的值，如果指定了一个很少会用到的值，那比不指定可能更惨。

但是不管使用什么方式，都应该在生产环境规模的数据（最好能模拟生产环境的行为）上进行测试以确保执行计划和性能都能符合你的期望。

基于参数嗅探的原因，你需要做类似下面的测试：

?

EXEC你的SP@custno = 123
EXEC你的SP@natregno = '1234567890'
EXEC你的SP@custname = 'ABC'
EXEC sp_recompile你的SP   -- 清空存储过程缓存
EXEC你的SP@natregno = '1234567890'
EXEC你的SP@custno = 123
EXEC你的SP@custname = 'ABC'
EXEC sp_recompile你的SP
EXEC你的SP@custname = 'ABC'
EXEC你的SP@custno = 123
EXEC你的SP@natregno = '1234567890'

?

也就是说，你需要测试所有参数在单独作为首次执行计划生成时的参数的情况，如果参数很多，你能想象需要测试的次数也很多。注意上面的sp_recompile，是为了通过重编译清空计划缓存以便减少计划缓存带来的影响。

在上面例子中，如果用户使用@custname参数传入的字符串中添加了%，这种情况下，扫描可能是更好的方式。如果你需要支持查询字符串前面有%的查询，最好的方式是拆成两个分支，如：

?

IF left(@custname, 1) <> '%'
   -- 上面的查询
ELSE
   -- 带有OPTIMIZE FOR的查询语句

?

?

小结：

?

目前为止，我们看到使用IF来实现一定程度的动态查询。这种方式不是很适合条件非常多的情况，正如前面所示，你要测试和编写的条件非常多，可能导致代码庞大不便于维护。但是对于简单的情况，比如2、3种参数时，这种方式却很有效，后续会介绍其他情况下的应对措施。

OR：

如果你不想使用多个IF判断，并且可以忽略前面提到的字符串前加%的情况，那么语句完全可以改写成类似下面的方式：

?

SELECT TOP 200 ...
FROM   customers
WHERE  (custno = @custno AND @custno IS NOT NULL) OR
       (natregno = @natregno AND @natregno IS NOT NULL) OR
       (custname LIKE @custname + '%' AND @custname IS NOT NULL)
ORDER  BY custname

?

?