技术教程 编程代码 T-SQL动态查询（3）——静态SQL 正文

T-SQL动态查询（3）——静态SQL

2023-07-11,,

接上文：T-SQL动态查询（2）——关键字查询   本文讲述关于静态SQL的一些知识和基础技巧。

简介：

什么是静态SQL？静态SQL是和动态SQL相对而言的，其实我们没必要过于纠结精确定义，只要大概知道什么算静态SQL即可。当一个语句特别是存储过程，语句不需要动态生成或拼接，除了参数之外我们都知道语句的最终形态时，就可以认为这是静态SQL，简单来说，我们大部分的）——关键字查询

使用带有OPTION(RECOMPILE)的静态SQL有以下优点：

对于中等复杂的搜索条件，可以得到排版紧凑，相对易于维护的代码。
由于查询每次都要重编译，得到的执行计划是针对当前查询条件进行优化的。
权限问题，这种方式在存储过程中总是可行的，也就是说只要用户有权限执行存储过程即可，不需要有直接操作表的权限。

当然，有优点就肯定有缺点：

当需求变得越来越复杂时，语句的复杂度会趋于非线性增长，甚至没有人会想到曾经一个简单的查询会变成如此复杂和难以理解。
如果查询被极其频繁地调用，过度编译、重编译会严重增加服务器的负担甚至导致服务器崩溃。

注意：OPTION(RECOMPILE)从SQL 2008 SP2/SQL 2008 R2 SP1及后续版本中才真正起效。

本文处处：http://blog.csdn.net/dba_huangzj/article/details/50056419

基础技能：

回顾一下第一篇：T-SQL动态查询（1）——简介 。里面提到的存储过程模版sp_Get_orders，我们在这里暂且不讨论存储过程中的@employeestr和@employeetbl参数。修改一下存储过程中的WHERE ???为以下条件，并以sp_Get_orders_1为新存储过程名：

CREATE PROCEDURE sp_Get_orders_1
                 @salesorderid     int     = NULL,
                 @fromdate    datetime     = NULL,
                 @todate      datetime     = NULL,
                 @minprice    money        = NULL,
                 @maxprice    money        = NULL,
                 @custid      int          = NULL,
                 @custname    nvarchar(40) = NULL,
                 @prodid      int          = NULL,
                 @prodname    nvarchar(40) = NULL
AS
SELECT o.SalesOrderID, o.OrderDate, od.UnitPrice, od.OrderQty,
       c.CustomerID, per.FirstName as CustomerName,p.ProductID,
       p.Name as ProductName,  per.BusinessEntityID as EmpolyeeID
FROM   Sales.SalesOrderHeader o
INNER JOIN   Sales.SalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
INNER JOIN   Sales.Customer c ON o.CustomerID = c.CustomerID
INNER JOIN   Person.Person per on c.PersonID=per.BusinessEntityID
INNER JOIN   Production.Product p ON p.ProductID = od.ProductID
WHERE  (o.SalesOrderID = @salesorderid OR @salesorderid IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
  AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.Name LIKE @prodname + '%' OR @prodname IS NULL)
ORDER  BY o.SalesOrderID
OPTION (RECOMPILE)
GO

本文处处：http://blog.csdn.net/dba_huangzj/article/details/50056419

其中@xxxx IS NULL子句的作用是当一个输入参数为NULL时，相应的AND条件总为TRUE。因此，对WHERE子句起效的条件就是为非NULL的值。虽然看起来挺简单，但是带有或不带有最后一句OPTION(RECOMPILE)会在性能上有巨大差异。

这个提示指示SQL Server每次都重编译该查询，如果不使用这个查询提示，SQL Server默认会产生执行计划并缓存以供重用。记住每次重编译的另外一个结果就是不缓存执行计划。

但是由于参数嗅探的原因，执行计划通常会针对第一次传入的参数进行优化和产生执行计划并缓存。珍重特性很难支持其他不同的参数。当用户传入一个单一订单号时，为了获取最佳响应时间，我们希望优化器使用Sales.SalesOrderHeader表和Sales.SalesOrderDetail表上的SalesOrderID列的索引而忽略其他所以。但如果用户传入产品号或产品名时，我们希望使用Production.Product表上的ProductID列的索引，其他条件类似。

这正是使用OPTION(RECOMPILE)提示的原因。由于SQLServer被命令每次都重编译查询，所以没有必要缓存执行计划，SQLServer会把所有变量按常量形式处理，比如这样调用存储过程：

EXEC sp_Get_orders_1@salesorderid = 11000

SQL Server优化时实际上的WHERE子句为：

WHERE  (o.SalesOrderID = 11000 OR 11000 IS NULL)
  AND  (o.OrderDate >= NULL OR NULL IS NULL)
  AND  (o.OrderDate <= NULL OR NULL IS NULL)
  AND  (od.UnitPrice >= NULL OR NULL IS NULL)
  AND  (od.UnitPrice <= NULL OR NULL IS NULL)
  AND  (o.CustomerID = NULL OR NULL IS NULL)

SQL Server 内部会把这些NULL ISNULL的条件从语句中移除，所以本质上的WHERE子句为：

WHERE  o.SalesOrderID = 11000

这样，优化器不需要考虑其他因素，直接使用SalesOrderID上的所以来支持查询。同样，如果以这种方式调用存储过程：

EXEC sp_Get_orders_1@custid = 123

则实际WHERE条件变为：

WHERE  (o.SalesOrderID = NULL OR NULL IS NULL)
  AND  (o.OrderDate >= NULL OR NULL IS NULL)
  AND  (o.OrderDate <= NULL OR NULL IS NULL)
  AND  (od.UnitPrice >= NULL OR NULL IS NULL)
  AND  (od.UnitPrice <= NULL OR NULL IS NULL)
  AND  (o.CustomerID = 123 OR 123 IS NULL)
  ...

本文处处：http://blog.csdn.net/dba_huangzj/article/details/50056419

此时优化器觉得使用CustomerID上的所以更加有效。可以使用下面测试语句来测试各种执行计划：

EXEC sp_Get_orders_1@prodid  = 76
EXEC sp_Get_orders_1@prodid  = 76, @custid = 123
EXEC sp_Get_orders_1@fromdate = '20080205', @todate = '20080209'

下面来看看两个比较有意思的调用方式：

首先创建一个索引：

use AdventureWorks2008R2
go
create index IX_SalesOrderHeader_OrderDate on Sales.SalesOrderHeader(OrderDate)

然后执行下面两个存储过程：

exec sp_Get_orders_1@fromdate='20050701',@todate ='20050701'
exec sp_Get_orders_1@fromdate='20050101',@todate ='20051231'

这两个存储过程调用具有相同的参数种类，但是产生了不同的查询计划。第一个查询某一天（注意@fromdate,@todate是同一天）的某个客户的订单。而第二个查询是查询某个客户全年的订单。

下图是第一个查询的执行计划：

这是第二个存储过程的执行计划：

如果觉得看不清楚的话读者可以自行运行前面的代码。为了更好地理解OPTION(RECOMPILE)，可以把上面的存储过程中的OPTION(RECOMPILE)查询提示注释掉，并新建一个新存储过程：

CREATE PROCEDURE sp_Get_orders_2
                 @salesorderid     int     = NULL,
                 @fromdate    datetime     = NULL,
                 @todate      datetime     = NULL,
                 @minprice    money        = NULL,
                 @maxprice    money        = NULL,
                 @custid      int		   = NULL,
                 @custname    nvarchar(40) = NULL,
                 @prodid      int          = NULL,
                 @prodname    nvarchar(40) = NULL
AS
SELECT o.SalesOrderID, o.OrderDate, od.UnitPrice, od.OrderQty,
       c.CustomerID, per.FirstName as CustomerName,p.ProductID,
       p.Name as ProductName,  per.BusinessEntityID as EmpolyeeID
FROM   Sales.SalesOrderHeader o
INNER JOIN   Sales.SalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
INNER JOIN   Sales.Customer c ON o.CustomerID = c.CustomerID
INNER JOIN   Person.Person per on c.PersonID=per.BusinessEntityID
INNER JOIN   Production.Product p ON p.ProductID = od.ProductID
WHERE  (o.SalesOrderID = @salesorderid OR @salesorderid IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
  AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.Name LIKE @prodname + '%' OR @prodname IS NULL)
ORDER  BY o.SalesOrderID
--OPTION(RECOMPILE)
GO

本文处处：http://blog.csdn.net/dba_huangzj/article/details/50056419

首先运行下面语句以便确保存储过程没有执行计划缓存：

EXEC sp_recompilesp_Get_orders_2

现在使用 @salesorderid=43659来运行新存储过程，然后使用@prodid=900来运行，会感觉有点慢，然后再使用sp_recompile冲掉执行计划然后以相反顺序，也就是先@prodid然后再@salesorderid的顺序执行，你会发现获取单一订单的时间反而很慢。这是因为执行计划是按第一个参数优化的，所以不能很好地支持第二个参数——但这种特性不影响结果的正确性。

联合陷阱：

相对于使用OR而言，很多人更偏向于使用下面方式实现：

o.orderID = coalesce(@SalesOrderID , o.OrderID)

或

o.orderID = isnull(@SalesOrderID, o.OrderID)

关于coalesce，可以看本人另外一篇文章：http://blog.csdn.net/dba_huangzj/article/details/8300784 在微软MCSA认证的70-461官方教材中有提到类似的描述。Coalesce()函数是一个很有用的函数，把一个参数列作为函数的参数，并返回列表中第一个非NULL的值，如果结果完全没有非NULL值（即均为NULL），则返回NULL。因此，如果@SalesOrderID为NULL，最终得到的是o.orderID=o.OrderID，一个恒为TRUE或者说直接跳过的操作符，注意关系数据库理论中谓词逻辑的WHERE子句中，只返回TRUE的条件，对于FALSE或者UNKNOWN的条件不返回。可以把上面存储过程改写成下面这样子来演示这种情况：

USE [AdventureWorks2008R2]
GO
CREATE PROCEDURE [dbo].[sp_Get_orders_3]
                 @salesorderid     int     = NULL,
                 @fromdate    datetime     = NULL,
                 @todate      datetime     = NULL,
                 @minprice    money        = NULL,
                 @maxprice    money        = NULL,
                 @custid      int          = NULL,
                 @custname    nvarchar(40) = NULL,
                 @prodid      int          = NULL,
                 @prodname    nvarchar(40) = NULL
AS
SELECT o.SalesOrderID, o.OrderDate, od.UnitPrice, od.OrderQty,
       c.CustomerID, per.FirstName as CustomerName,p.ProductID,
       p.Name as ProductName,  per.BusinessEntityID as EmpolyeeID
FROM   Sales.SalesOrderHeader o
INNER JOIN   Sales.SalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
INNER JOIN   Sales.Customer c ON o.CustomerID = c.CustomerID
INNER JOIN   Person.Person per on c.PersonID=per.BusinessEntityID
INNER JOIN   Production.Product p ON p.ProductID = od.ProductID
WHERE  o.SalesOrderID = coalesce(@SalesOrderID, o.SalesOrderID)
  AND  o.OrderDate >= coalesce(@fromdate, o.OrderDate)
  AND  o.OrderDate <= coalesce(@todate, o.OrderDate)
  AND  od.UnitPrice >= coalesce(@minprice, od.UnitPrice)
  AND  od.UnitPrice <= coalesce(@maxprice, od.UnitPrice)
  AND  o.CustomerID = coalesce(@custid, o.CustomerID)
  AND  od.ProductID = coalesce(@prodid, od.ProductID)
  AND  p.Name LIKE coalesce(@prodname, '') + '%'
  AND  per.FirstName LIKE coalesce(@custname, per.FirstName) + '%'
ORDER  BY o.SalesOrderID
OPTION (RECOMPILE)

本文处处：http://blog.csdn.net/dba_huangzj/article/details/50056419

虽然代码看上去更加简洁，但是强烈建议不要使用这种方法，因为这里有一个陷阱，为了实现这种情况，我做了几个小动作：

1．  在新存储过程最后ORDERBY子句上面添加了一个对FirstName的条件，因为后续要用。

2．  使用下面语句把表结构改动一下，直接执行下面语句：

use AdventureWorks2008R2
go
begin tran --添加事物以便回滚
alter table Person.Person alter column FirstName nvarchar(50)  null --修改表结构使其允许NULL
go

update Person.Person set FirstName = null where BusinessEntityID=18351--特意更新为null

EXEC sp_Get_orders_1@salesorderid =75118  --查询刚才BusinessEntityID=18351的信息，该信息对应的SalesOrderID=75118
EXEC sp_Get_orders_3@salesorderid = 75118

rollback --回滚刚才操作

你应该会看到如下结果：

其实从上面修改结构的操作中或多或少已经能猜出，是因为有NULL，当数据中存在NULL并且使用coalesce函数时，条件变成了：

per.FirstName LIKE coalesce(@custname, per.FirstName) + '%'

本文处处：http://blog.csdn.net/dba_huangzj/article/details/50056419

实际上变成了NULL LIKE NULL，不用纠结LIKE 和=操作符。在SQL中，NULL不等于NULL，LIKE也同理，NULL是未知值，前面说过WHERE条件仅返回为TRUE的数据，对非TRUE的任何条件都不返回。因此，没有返回数据。为了避免这种陷阱，有人改写成：

coalesce(per.FirstName, '')=coalesce(@custname, per.FirstName, '')

虽然有效，但是这样就导致了索引无法被优化器使用，导致性能问题。另一个变动的办法是改成成(xx =@xx or @xx is null)的形式。但是最简单的解决办法还是完全避免这种写法。

处理多值参数：

下面是时候看看@employeestr和@employeetbl参数的处理。后续我们将需要使用一个函数：

CREATE FUNCTION intlist_to_tbl (@listnvarchar(MAX))
  RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                       number  int NOT NULL) AS
BEGIN
  DECLARE @startpos int,
          @endpos   int,
          @textpos  int,
           @chunklen smallint,
          @str      nvarchar(4000),
          @tmpstr   nvarchar(4000),
          @leftover nvarchar(4000)

   SET@textpos = 1
   SET@leftover = ''
  WHILE @textpos <= datalength(@list) / 2
  BEGIN
     SET @chunklen = 4000 - datalength(@leftover) / 2
     SET @tmpstr = ltrim(@leftover +
                    substring(@list, @textpos,@chunklen))
     SET @textpos = @textpos + @chunklen

     SET @startpos = 0
     SET @endpos = charindex(',' COLLATE Slovenian_BIN2, @tmpstr)

     WHILE @endpos > 0
     BEGIN
        SET @str = substring(@tmpstr, @startpos + 1,
                              @endpos -@startpos - 1)
        IF @str <> ''
           INSERT @tbl (number) VALUES(convert(int, @str))
        SET @startpos = @endpos
        SET @endpos = charindex(',' COLLATE Slovenian_BIN2,
                                 @tmpstr,@startpos + 1)
     END

     SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
   END

   IFltrim(rtrim(@leftover)) <> ''
     INSERT @tbl (number) VALUES(convert(int, @leftover))

  RETURN
END
Go

同时还要创建一个用户自定义数据类型：

DECLARE @version varchar(30)
SELECT @version = convert(varchar(30),serverproperty('ProductVersion'))
IF convert(int, substring(@version, 1,charindex('.', @version) - 1)) >= 10
  EXEC('CREATE TYPE intlist_tbltype AS TABLE (val int NOT NULL PRIMARYKEY)')
go

这两个参数在WHERE条件中的语句是：

AND (o.EmployeeID IN (SELECT number FROM intlist_to_tbl(@employeestr)) OR
     @employeestr IS NULL)
AND (o.EmployeeID IN (SELECT val FROM @employeetbl) OR @hasemptbl = 0)

并且在将要使用的新存储过程的开头要加上一个局部变量：@hasemptbl

DECLARE @hasemptbl bit = CASE WHEN EXISTS(SELECT * FROM @employeetbl)
                               THEN 1
                               ELSE 0
                          END

这个变量用于提示优化器，如果@employeetbl没有数据，则可以直接跳过，减少不必要的优化开销。但是这种写法如果是在语句主体的EXISTS中使用，则不起效。下面新建一个存储过程，包含上面提到的两个参数：

CREATE PROCEDURE sp_Get_orders_4
               @salesorderid     int     = NULL,
                 @fromdate    datetime     = NULL,
                 @todate      datetime     = NULL,
                 @minprice    money        = NULL,
                 @maxprice    money        = NULL,
                 @custid      int     = NULL,
                 @custname    nvarchar(40) = NULL,
                 @prodid      int          = NULL,
                 @prodname    nvarchar(40) = NULL,
               @employeestrvarchar(MAX) = NULL,
               @employeetblintlist_tbltype READONLYAS

    DECLARE @hasemptbl bit = CASE WHEN EXISTS(SELECT * FROM @employeetbl)
                               THEN 1
                               ELSE 0
                          END
    SELECT o.SalesOrderID, o.OrderDate, od.UnitPrice, od.OrderQty,
          c.CustomerID, per.FirstName as CustomerName,p.ProductID,
          p.Name as ProductName,  per.BusinessEntityID as EmpolyeeID
    FROM   Sales.SalesOrderHeader o
    INNER JOIN   Sales.SalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
    INNER JOIN   Sales.Customer c ON o.CustomerID = c.CustomerID
    INNER JOIN   Person.Person per on c.PersonID=per.BusinessEntityID
    INNER JOIN   Production.Product p ON p.ProductID = od.ProductID
    WHERE  o.SalesOrderID = coalesce(@SalesOrderID, o.SalesOrderID)
         AND  o.OrderDate >= coalesce(@fromdate, o.OrderDate)
         AND  o.OrderDate <= coalesce(@todate, o.OrderDate)
         AND  od.UnitPrice >= coalesce(@minprice, od.UnitPrice)
         AND  od.UnitPrice <= coalesce(@maxprice, od.UnitPrice)
         AND  o.CustomerID = coalesce(@custid, o.CustomerID)
         AND  od.ProductID = coalesce(@prodid, od.ProductID)
         AND  p.Name LIKE coalesce(@prodname, '') + '%'
         AND  per.FirstName LIKE coalesce(@custname, per.FirstName) + '%'
         AND  (per.BusinessEntityID IN(SELECT number FROM dbo.intlist_to_tbl(@employeestr)) OR
               @employeestr IS NULL)
         AND  (per.BusinessEntityID IN(SELECT val FROM @employeetbl) OR @hasemptbl = 0)
        ORDER  BY o.SalesOrderID
 OPTION (RECOMPILE)

然后用下面两个语句测试一下：

EXEC sp_Get_orders_4 @employeestr = '1045,1417,1205', @custid = 29898

DECLARE @tbl intlist_tbltype
INSERT @tbl (val) VALUES(1045), (1417), (1205)
EXEC sp_Get_orders_4 @employeetbl = @tbl, @custid = 29898

均返回37个订单。

虽然看上去能运行，但是于常规的简单标量参数不同，对于后者，优化器知道参数的实际值并建立相应的执行计划。正如前面看到的日期区间的例子一样，相同的参数集但不同的参数值会产生不同的执行计划。但是在search_orders_4 中的多值变量却不会这样。优化器知道@employeetbl是包含了一定行数的表变量。而针对@employeestr，优化器不知道里面的信息，如果查询中的值的数据分布想到那个均匀，这种情况通常不撑问题，但是如果数据分布严重倾斜，就会产生严重的问题。

其中一个处理方法是把数据插到临时表。因为临时表有相关数据分布的统计信息，可以提供优化器优化只用。但是如果仅仅插入少量值，就需要自己强制更新统计信息。因为临时表的自动统计信息首次更新需要插入/修改6行或以上的数据才发生。

下面来调整一下这个存储过程，其思路是大部分时间假定用户仅希望同时查询1~3个值的数据。因此，如果列表中有不超过4个元素，存储过程用IN表达式，否则使用表变量：

CREATE PROCEDURE sp_Get_orders_5
@salesorderid     int     = NULL,
@fromdate    datetime     = NULL,
@todate      datetime     = NULL,
@minprice    money        = NULL,
@maxprice    money        = NULL,
@custid      int         = NULL,
@custname    nvarchar(40) = NULL,
@prodid      int          = NULL,
@prodname    nvarchar(40) = NULL,
@employeestr varchar(MAX) = NULL
AS

DECLARE @employeetbl TABLE (rowno int NOT NULL PRIMARY KEY,employeeid int NOT NULL UNIQUE)

DECLARE @rowc      int,
        @emp1      int,
        @emp2      int,
        @emp3      int,
        @emp4      int,
        @hasemptblbit = 0

IF @employeestr IS NOT NULL
BEGIN
   INSERT @employeetbl(rowno, employeeid)
      SELECT row_number() OVER(ORDER BY (SELECT 1)), number
      FROM   intlist_to_tbl(@employeestr)
   SELECT @rowc = @@rowcount

   IF @rowc BETWEEN 1 AND 4
   BEGIN
      SELECT @emp1 = employeeid FROM @employeetbl WHERE rowno = 1
      SELECT @emp2 = employeeid FROM @employeetbl WHERE rowno = 2
      SELECT @emp3 = employeeid FROM @employeetbl WHERE rowno = 3
      SELECT @emp4 = employeeid FROM @employeetbl WHERE rowno = 4
   END
   ELSE IF @rowc > 4
      SELECT @hasemptbl = 1
END

SELECT o.SalesOrderID, o.OrderDate, od.UnitPrice, od.OrderQty,
       c.CustomerID, per.FirstName as CustomerName,p.ProductID,
       p.Name as ProductName,  per.BusinessEntityID as EmpolyeeID
FROM   Sales.SalesOrderHeader o
    INNER JOIN   Sales.SalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
    INNER JOIN   Sales.Customer c ON o.CustomerID = c.CustomerID
    INNER JOIN   Person.Person per on c.PersonID=per.BusinessEntityID
    INNER JOIN   Production.Product p ON p.ProductID = od.ProductID
WHERE  (o.salesorderid = @salesorderid OR @salesorderid IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
  AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.Name  LIKE @prodname + '%' OR @prodname IS NULL)
  AND  (per.BusinessEntityID IN(@emp1, @emp2, @emp3, @emp4) OR @emp1 IS NULL)
  AND  (per.BusinessEntityID IN(SELECT employeeid FROM @employeetbl) OR @hasemptbl = 0)
ORDER  BY o.salesorderid
OPTION (RECOMPILE)

为了简化问题，上面存储过程仅使用@employeestr参数，而@employeetbl被重构成本地工作表。代码前部分的：

DECLARE @rowc   int,
        @emp1  int,
       @emp2   int,
       @emp3   int,
       @emp4   int,
       @emptbl bit = 0

IF @employeestr IS NOT NULL
BEGIN
  INSERT @employeetbl (rowno, employeeid)
     SELECT row_number() OVER(ORDER BY (SELECT 1)), number
     FROM   intlist_to_tbl(@employeestr)
  SELECT @rowc = @@rowcount

   IF@rowc BETWEEN 1 AND 4
  BEGIN
     SELECT @emp1 = employeeid FROM @employeetbl WHERE rowno = 1
     SELECT @emp2 = employeeid FROM @employeetbl WHERE rowno = 2
     SELECT @emp3 = employeeid FROM @employeetbl WHERE rowno = 3
     SELECT @emp4 = employeeid FROM @employeetbl WHERE rowno = 4
   END
   ELSEIF @rowc > 4
     SELECT @emptbl = 1
END

如果@employeestr有一个值，就把它拆分成一个表变量并记录行数。如果小于等于4个值，就填充变量@emp1到@emp4。那么WHERE子句的最后两个条件就成了：

AND  (per.BusinessEntityID IN(@emp1, @emp2, @emp3, @emp4) OR @emp1 IS NULL)
AND  (per.BusinessEntityID IN(SELECT employeeid FROM @employeetbl) OR @hasemptbl = 0)

也就是说，如果@emp1有值，则第一个条件生效，并且包含了1~4个值的列表。当列表包含5个或以上值时并且@hasemptbl为1时，仅有第一个条件生效。这里需要提醒一下，4这个值不是固定或者规定，你可以限制1~10的值，甚至更多，但是在IN中，一般不应该包含太多列值，否则影响性能甚至无法生成执行计划，这一点在联机丛书的IN 关键字中也有提到。

本文处处：http://blog.csdn.net/dba_huangzj/article/details/50056419

选择订单排列顺序：

如果要允许用户选择订单的排序顺序，在静态SQL中很容易实现，如：

ORDER BY CASE @sortcol WHEN 'SalesOrderID'      THEN o.SalesOrderID
                       WHEN'EmployeeID'  THEN per.BusinessEntityID
                       WHEN'ProductID'   THEN p.ProductID
         END,
         CASE @sortcol WHEN 'CustomerName' THEN per.FirstName
                       WHEN'ProductName' THEN p.Name
         END,
         CASE @sortcol WHEN 'OrderDate'    THEN o.OrderDate
        END

也就是说，可以使用一个参数来包含要排序的列或其他映射到列明的标识符，然后通过CASE WHEN 来选择最终要排序的列。需要提醒的是，在CASE 表达式的分支中，必须是相同或可转换的数据类型，并且数据类型的优先级会对结果有影响。在THEN表达式中，低优先级会转换成CASE中最高优先级的数据类型。如果你在同一个CASE表达式中混合字符串和数值型的列，并尝试排序字符串列，会产生转换错误。所以在这种情况下，你需要使用一个针对数值型列的CASE表达式和一个针对字符串的CASE表达式，甚至还要针对日期型的。

如果你希望同时支持升序和降序排序，同样需要使用双重写法，如：

ORDER BY CASE WHEN @isdesc = 1 THEN
              CASE@sortcol WHEN 'OrderID'    THEN o.SalesOrderID
                            WHEN 'EmployeeID' THEN per.BusinessEntityID
                            WHEN 'ProductID'  THEN p.ProductID
         END DESC,
         CASE WHEN @isdesc = 0 THEN
              CASE@sortcol WHEN 'OrderID'    THEN o.SalesOrderID
                            WHEN 'EmployeeID' THEN per.BusinessEntityID
                            WHEN 'ProductID'  THEN p.ProductID
         END ASC

从上面例子可以预估，当你需要很多不同类型的排序时，使用静态SQL可能会导致代码完全不可控，这种情况下可以考虑使用动态SQL来实现。

候选表：

有些情况下，你仅需要在特定条件生效时才去访问一个表。下面在存储过程中再添加一个参数：@Territorname，通过提供这个参数，存储过程需要返回特定区域的订单信息（注意这里只是多了个筛选条件，返回的列是一样的，后续再演示不同列的情况），也就是关联表Sales.SalesTerritory。但是这里的方法是使用EXISTS子句：

AND  (@Territorname IS NULL OR EXISTS(SELECT *
                                  FROM   Sales.SalesTerritory sst
                                  WHERE  sst.TerritoryID = o.TerritoryID
                                    AND  sst.Name    = @Territorname))

作为演示，再修改前面的存储过程，但是没有查询employees的相关参数：

CREATE PROCEDURE sp_Get_orders_6
              @salesorderid     int     = NULL,
			  @fromdate    datetime     = NULL,
			  @todate      datetime     = NULL,
			  @minprice    money        = NULL,
			  @maxprice    money        = NULL,
			  @custid      int          = NULL,
			  @custname    nvarchar(40) = NULL,
			  @prodid      int          = NULL,
			  @prodname    nvarchar(40) = NULL,
			  @Territornamenvarchar(15) = NULL AS

SELECT o.SalesOrderID, o.OrderDate, od.UnitPrice, od.OrderQty,
       c.CustomerID, per.FirstName as CustomerName,p.ProductID,
       p.Name as ProductName,  per.BusinessEntityID as EmpolyeeID
FROM   Sales.SalesOrderHeader o
    INNER JOIN   Sales.SalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
    INNER JOIN   Sales.Customer c ON o.CustomerID = c.CustomerID
    INNER JOIN   Person.Person per on c.PersonID=per.BusinessEntityID
    INNER JOIN   Production.Product p ON p.ProductID = od.ProductID
WHERE  (o.salesorderid = @salesorderid OR @salesorderid IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
  AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.Name  LIKE @prodname + '%' OR @prodname IS NULL)
  AND  (@Territorname IS NULL OR EXISTS(SELECT *
                                  FROM   Sales.SalesTerritory sst
                                  WHERE  sst.TerritoryID = o.TerritoryID
                                    AND  sst.Name    = @Territorname))
ORDER  BY o.SalesOrderID
OPTION (RECOMPILE)

开启实际执行计划后，运行下面两个存储过程：

set statistics io on ;
EXEC sp_Get_orders_6@custname = 'James'
set statistics io off;
set statistics io on ;
EXEC sp_Get_orders_6@custname = 'James',@Territorname = 'Northeast'
set statistics io off;

可以看到执行计划和开销相同，但是打开第二个选项页查看IO情况，可以看到如下结果：

注意第一个存储过程并没有访问”SalesTerritory”表，而第二个访问了该表。回过头查看刚才的执行计划，可以发现在”SalesTerritory”聚集索引查找操作符上方有一个“筛选器”操作符，鼠标移到这个操作符上可以看到下面的信息，注意上面的“启动表达式谓词，Startup Expression Predicate”：

这个操作符意味着在SQL Server在运行过程中决定是否需要访问这个表。在某种程度上和OPTION(RECOMPILE)的效果是一样的，当你遇到你的查询条件依赖于一个参数来决定是否与特定表进行筛选时，这种方式是不错的选择。但是如果一个带有启动表达式筛选条件能起效，那么使用OPTION(RECOMPILE)将会带来过度使用，所以应该坚持测试每一个可能的执行计划并检查性能是否可接受。

备用表：

这个跟上面的候选表有一些差异，根据参数的不同，你需要同不同表中读取数据，而上面候选表是基于是否要“关联”。比如一个参数@ishistoric，如果为1，就需要访问Sales.HistorySalesOrderHeader表和Sales.HistorySalesOrderDetail表（注意没有这两个表，我们只是假设有）。可以把存储过程的FROM部分改写为下面样子：

FROM (SELECT o.SalesOrderID, o.OrderDate, od.UnitPrice, od.OrderQty
FROM   Sales.SalesOrderHeader o
    INNER JOIN   Sales.SalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
WHERE @ishistoric = 0
UNION ALL
SELECT o.SalesOrderID, o.OrderDate, od.UnitPrice, od.OrderQty
FROM   Sales.HistorySalesOrderHeader o
    INNER JOIN   Sales.HistorySalesOrderDetailod ON o.SalesOrderID = od.SalesOrderID
WHERE @ishistoric = 1)AS u
INNER JOIN ....

通过OPTION(RECOMPILE)，在运行过程仅有一个关于SalesOrder的结果集被访问，即使没有这个提示，也可以通过“启动表达式”来实现。但是可以看到，语句有一定程度的重复，特别是SELECT和JOIN部分。越多备用表，静态SQL就增长得越复杂，使用动态SQL能避免很多这类重复。

OPTION(RECOMPILE)的缺点：

本文绝大部分都是讨论OPTION(RECOMPILE)在静态SQL中的作用和优点，但是正如本人一直坚持的观点，有好处/优点就一定有坏处/缺点。在打算广泛使用某个功能之前，你要明白过于轻率地使用是否会伤害服务器。这也是其中一个SQL Server通常情况下会缓存执行计划的原因之一。没有适当的计划缓存，大量查询编译会导致服务器崩溃。

首先你需要了解查询的执行频率，或者用户每分钟运行存储过程的峰值。一半情况下，编译时间可以忽略，但是对于一个每秒可能会超过100次调用的存储过程来说，如果50ms的编译时间可以省略的话，执行时间可能可以从5分钟降到10ms。

再进一步假设，如果@salesorderid是在输入参数中唯一一个被大量调用的参数，那么每次查询都因为重编译产生相同的执行计划会明显浪费CPU资源。其中一个缓解方案是引入IF语句：

IF @salesorderid IS NOT NULL
BEGIN
   SELECT ...
   WHERE  O.salesorderid = @salesorderid
     AND  -- 其他条件.
   -- 注意没有OPTION(RECOMPILE)!
END
ELSE
BEGIN
   SELECT ...
   WHERE  -- 其他条件
   OPTION (RECOMPILE)
END

通过对不包含@salesorderid的分支使用OPTION(RECOMPILE)，单个订单的查询请求可以通过缓存的执行计划实现，尽管其他条件每次都会编译。但是如果你把这种方式放到生产环境，你还是会发现编译依旧很高，这是定位出来的情况是因为@custid和@fromdate，并且@fromdate的值通常是当前日期的前一周。这样会导致更多的不带有OPTION(RECOMPILE)分支的出现。比如下面存储过程：

CREATE PROCEDURE sp_Get_orders_7
                 @salesorderid     int     = NULL,
              @fromdate    datetime     = NULL,
              @todate      datetime     = NULL,
              @minprice    money        = NULL,
              @maxprice    money        = NULL,
              @custid      int         = NULL,
              @custname    nvarchar(40) = NULL,
              @prodid      int          = NULL,
              @prodname    nvarchar(40) = NULL AS

-- 单个订单, 使用缓存的执行计划。
IF @salesorderid IS NOT NULL
BEGIN
SELECT o.SalesOrderID, o.OrderDate, od.UnitPrice, od.OrderQty,
       c.CustomerID, per.FirstName as CustomerName,p.ProductID,
       p.Name as ProductName,  per.BusinessEntityID as EmpolyeeID
    FROM   Sales.SalesOrderHeader o
       INNER JOIN   Sales.SalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
       INNER JOIN   Sales.Customer c ON o.CustomerID = c.CustomerID
       INNER JOIN   Person.Person per on c.PersonID=per.BusinessEntityID
       INNER JOIN   Production.Product p ON p.ProductID = od.ProductID
    WHERE (o.salesorderid = @salesorderid OR @salesorderid IS NULL)
--   AND  (o.OrderDate >= @fromdateOR @fromdate IS NULL)
--   AND  (o.OrderDate <= @todate OR@todate IS NULL)
      AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
      AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
     --AND  (o.CustomerID = @custid OR @custidIS NULL)
      AND  (od.ProductID = @prodid OR @prodid IS NULL)
      AND  (p.Name  LIKE @prodname + '%' OR @prodname IS NULL)
  ORDER  BY o.SalesOrderID
END
-- 特定时间特定客户的订单，使用计划缓存。
ELSE IF @custid IS NOT NULL AND
     datediff(DAY, @fromdate, getdate()) <= 7
BEGIN
   SELECT o.SalesOrderID, o.OrderDate, od.UnitPrice, od.OrderQty,
       c.CustomerID, per.FirstName as CustomerName,p.ProductID,
       p.Name as ProductName,  per.BusinessEntityID as EmpolyeeID
    FROM   Sales.SalesOrderHeader o
       INNER JOIN   Sales.SalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
       INNER JOIN   Sales.Customer c ON o.CustomerID = c.CustomerID
       INNER JOIN   Person.Person per on c.PersonID=per.BusinessEntityID
       INNER JOIN   Production.Product p ON p.ProductID = od.ProductID
    WHERE --(o.salesorderid = @salesorderid OR @salesorderid IS NULL)
--   AND
       (o.OrderDate >= @fromdate OR @fromdate IS NULL)
       AND  (o.OrderDate <= @todate OR @todate IS NULL)
       AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
       AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
       AND  (o.CustomerID = @custid OR @custid IS NULL)
       AND  (od.ProductID = @prodid OR @prodid IS NULL)
       AND  (p.Name  LIKE @prodname + '%' OR @prodname IS NULL)
  ORDER  BY o.SalesOrderID
END
ELSE
-- 除此之外的全部每次都编译。
BEGIN
   SELECT o.SalesOrderID, o.OrderDate, od.UnitPrice, od.OrderQty,
       c.CustomerID, per.FirstName as CustomerName,p.ProductID,
       p.Name as ProductName,  per.BusinessEntityID as EmpolyeeID
    FROM   Sales.SalesOrderHeader o
       INNER JOIN   Sales.SalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
       INNER JOIN   Sales.Customer c ON o.CustomerID = c.CustomerID
       INNER JOIN   Person.Person per on c.PersonID=per.BusinessEntityID
       INNER JOIN   Production.Product p ON p.ProductID = od.ProductID
    WHERE (o.salesorderid = @salesorderid OR @salesorderid IS NULL)
      AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
      AND  (o.OrderDate <= @todate OR @todate IS NULL)
      AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
      AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
      AND  (o.CustomerID = @custid OR @custid IS NULL)
      AND  (od.ProductID = @prodid OR @prodid IS NULL)
      AND  (p.Name  LIKE @prodname + '%' OR @prodname IS NULL)
  ORDER  BY o.SalesOrderID
  OPTION(RECOMPILE)
END

注意我特意注释了不需要使用的WHERE子句部分，以便读者发现不同。但是这种存储过程并不见得就能运行得很好，因为当这个存储过程首次运行时是查询@prodid，那么两个分支中的OrderID和某个客户的近期订单会被优化用于查询ProductID，结果就是灾难性的性能问题。这种情况可以通过索引提示来解决。但是前面提过，索引提示会有风险，比如所以被删除、改名甚至修改定义等。或者把没有带有OPTION(RECOMPILE)的SELECT语句拆成子存储过程。如果你发现有更多常用的输入组合引起过多编译，这种方式将会失控，这时就要考虑动态SQL，后面将会介绍。

总结：

本文介绍了关于静态SQL的一些使用方式，主要介绍的是关于静态SQL中的OPTION(RECOMPILE)查询提示的使用知识。但是在本文中已经多处提到过，静态SQL对于简单的动态需求可以比较容易地应对，可是对于日趋复杂的需求，静态SQL将会导致性能和编码的不可控。此时就需要改写查询，或使用如动态SQL之类的方法实现。

另外对于OPTION(RECOMPILE)，使用时均要慎重，因为编译、重编译在OLTP系统中不应该长期、大量存在。否则直接影响服务器性能，严重时直接导致服务器崩溃。

当然，解决这些问题的最根本的方案是良好的数据库甚至系统架构设计，不过从实操上确实不现实。但是不管如何，对问题积累和思考，在适当的时候改进甚至重构，大部分系统都如此。

最后唠叨一句，不仅限于本文，在任何时候，在生产环境中对任何可能造成影响的修改都应该做充分的测试论证并做好回滚和文档准备。

T-SQL动态查询（3）——静态SQL的相关教程结束。

《T-SQL动态查询（3）——静态SQL.doc》

下载本文的Word格式文档，以方便收藏与打印。

Copyright 2023. 昆居客 - IT编程,互联网信息技术文档大全!