T-SQL动态查询（4）——动态SQL 前言： 为什么使用动态SQL： 动态SQL简单介绍： 动态SQL的參数化查询形式： 动态SQL的编译和缓存： 缓存问题： 总结：
前面说了非常多关于动态查询的内容。本文将介绍使用动态SQL解决动态查询的一些方法。
为什么使用动态SQL：
在非常多项目中，动态SQL被广泛使用甚至滥用。非常多时候，动态SQL又确实是解决非常多需求的首选方法。可是假设不合理地使用，会导致性能问题及无法维护。动态SQL尤其自己的优缺点。是否使用须要进行评估分析：
本文出处：http://blog.csdn.net/dba_huangzj/article/details/50202371
动态SQL长处：
- 动态SQL提供了强大的扩展功能，能够应付复杂的需求，即使在需求添加时也能应对，而且不会由于需求的添加而导致代码的线性增长。
- 运行计划能够缓存查询字符串，意味着大部分查询条件能够重用运行计划缓存而不会导致不必要的重编译。
动态SQL缺点：
- 不合理的编码会导致代码的维护陷入困境。
- 动态SQL是用于应对较高级的问题。对于简单问题，会变得大材小用。
- 动态SQL的測试显然比其它代码困难，特别是对终于运行的语句的获取，同一时候easy由于编码的不规范导致语法错误。
- 相对于前面的OPTION(RECOMPILE)。动态SQL须要加入对权限控制的考虑。
- 动态SQL的计划缓存并不总是你想象的那样。有时候由于输入的參数值而导致不同的计划生成。
静态SQL事实上能够应对大部分的日常需求。可是随着需求的添加，静态SQL会变得越来越复杂，同一时候可能带来过多的重编译，此时应该考虑动态SQL。
动态SQL简单介绍：
概述：
在SQL Server中，动态SQL能够由三种方式实现：
- T-SQL存储过程
- CLR存储过程
- client语句
本文着重介绍T-SQL中的存储过程。针对用户的输入，有两种方式进行处理：
- 把參数通过字符串拼接实现。如：' AND col = ' + convert(varchar, @value)'
- 使用sp_executesql进行參数化查询。能够把上面的參数变成：' AND col = @value'
基于非常多理由，在日常使用中。推荐使用第二种方法也就是sp_executesql。
可是须要提醒的是上面提到的三种实现动态SQL的方式没有本质上的好和坏。仅仅有依据实际情况而定才是最有效的。
本文将使用静态SQL篇中的需求作为演示，即针对不同的查询条件、不同的排序甚至不同的汇总需求演示。
本文出处：http://blog.csdn.net/dba_huangzj/article/details/50202371
权限：
对于存储过程中使用静态SQL，权限问题并无大碍。仅仅要存储过程的调用者和表的拥有者是同样的。由于全部权链（ownership chaining，https://msdn.microsoft.com/zh-cn/library/ms188676.aspx），能够无障碍地运行存储过程。
可是动态SQL中不存在全部权链，即使把它们放在存储过程中也一样，由于动态SQL有自己的权限范围。
假设在client程序或CLR存储过程中创建动态SQL，还须要额外授予用户具有查询中涉及到的表、视图、自己定义函数上的SELECT权限。
依据client程序和CLR存储过程的不同，权限链可能会非常混乱和失控。可是能够使用以下两种方式来应付：
- 创建一个证书，对存储过程使用这个证书进行签名。然后使用证书创建一个用户，并由于用户所需的SELECT权限。
- 在存储过程中加入EXECUTE AS ‘用户’。然后授予SELECT权限。
动态SQL的參数化查询形式：
本部分使用第一篇中提到的模版进行改造演示。为了能清晰地描写叙述。使用博客自带的行号来标号：
USE [AdventureWorks2008R2]
GO
CREATE PROCEDURE [dbo].[sp_Get_orders]
@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,
@employeetbl intlist_tbltypeREADONLY,
@debug bit =0
AS
DECLARE @sql nvarchar(MAX),
@paramlist nvarchar(4000),
@nl char(2) = char(13) + char(10)
SELECT @sql='
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 onc.PersonID=per.BusinessEntityID
INNER JOIN Production.Product p ON p.ProductID =od.ProductID
WHERE 1=1'+@nl
IF @salesorderidIS NOT NULL
SELECT @sql+= ' AND o.SalesOrderID=@SalesOrderID'+
' ANDod.SalesOrderID=@SalesOrderID'+@nl
IF @fromdateIS NOT NULL
SELECT @sql+= ' AND o.OrderDate >= @fromdate'+@nl
IF @todateIS NOT NULL
SELECT @sql+= ' AND o.OrderDate <= @todate'+@nl
IF @minpriceIS NOT NULL
SELECT @sql += 'AND od.UnitPrice >= @minprice' + @nl
IF @maxpriceIS NOT NULL
SELECT @sql += 'AND od.UnitPrice <= @maxprice' + @nl
IF @custidIS NOT NULL
SELECT @sql += 'AND o.CustomerID = @custid' +
' AND c.CustomerID = @custid' +@nl
IF @custnameIS NOT NULL
SELECT@sql += ' AND per.FirstName LIKE @custname + ''%''' + @nl
IF @prodidIS NOT NULL
SELECT@sql += ' AND od.ProductID = @prodid' +
' AND p.ProductID = @prodid' +@nl
IF @prodnameIS NOT NULL
SELECT@sql += ' AND p.Name LIKE @prodname + ''%''' + @nl
IF @employeestrIS NOT NULL
SELECT@sql += ' AND per.BusinessEntityID IN' +
' (SELECT number FROM dbo.intlist_to_tbl(@employeestr))'+ @nl
IF EXISTS(SELECT * FROM @employeetbl)
SELECT@sql += ' AND per.BusinessEntityID IN (SELECT val FROM @employeetbl)'+ @nl
SELECT @sql+= ' ORDER BYo.SalesOrderID' + @nl
IF @debug= 1
PRINT @sql
SELECT @paramlist= '@salesorderid int,
@fromdate datetime,
@todate datetime,
@minprice money,
@maxprice money,
@custid nchar(5),
@custname nvarchar(40),
@prodid int,
@prodname nvarchar(40),
@employeestr varchar(MAX),
@employeetbl intlist_tbltype READONLY'
EXEC sp_executesql@sql, @paramlist,@salesorderid, @fromdate, @todate, @minprice,
@maxprice, @custid, @custname,@prodid, @prodname, @employeestr, @employeetbl
代码分析：
在上面代码中的第18行。定义了一个变量@sql。用于存储查询字符串。
由于sp_executesql要求參数必须为NVARCHAR，所以这里使用NVARCHAR(MAX)，以便足够存放全部终于字符串。
在第20行，使用了一个变量@nl，通过赋值char(13)+char(10)实现Windows上的换行符功能。尽管它是变量，可是在存储过程中实际上是一个常量。
在第22 到31行。包括了动态SQL的核心部分，并存放在@sql变量中。通过兴许的參数拼接实现整个动态SQL查询。
注意代码中均使用了两部命名（即架构名.表名），由于由于性能原因。SQL Server在编译和优化时须要精确定位对象，假设表A存在dbo.A和Sales.A这两个架构名，那么SQL Server须要花时间去推断到底使用的是哪个表，这会带来不小的开销，注意。即使仅仅有几十毫秒，可是对于一个频繁被运行的存储过程或语句，总体性能会被明显拉低，所以无论基于性能还是编程规范的考虑，都应该带上架构名。当然假设你的系统仅仅有dbo这个默认架构，不带也行，可是建议还是要规范化编程提高可读性和可维护性。
这里再插一句，在本人优化的代码中。常常看到非常多语句中。表名使用了别名，可是在ON、WHERE中又没有带上别名前缀，咋一看上去非常难知道字段来自于哪个表，要一个一个相关表去检查。花了不该花的时间，为了维护代码的人，你们即可行好吧。
在第31行是一句“WHERE 1=1”，相似编程语言中的占位符。使WHERE语句即使单独存在也不会报错。以下会介绍为什么也要加上@nl。
在第33行開始。针对全部单值查询參数进行检查，假设參数不为NULL，则加入到终于的SQL字符串的相应列中。从这里開始就要注意对单引號、双引號的使用，同一时候留意在每次拼接后面都加上了@nl。
在第67 行，对@employeestr參数进行处理，处理方式和上一篇静态SQL一样。其它剩余部分相对简单，不做过多解释。
在第72行。加入了一个參数@debug。默觉得0，当用户调用传入1时，输出SQL字符串，这在调试和检查错误时非常实用，由于动态SQL往往非常难直接从代码中看出终于语句，假设在开发过程没有注意引號、空格、类型转换等问题时，都会在兴许调用过程中报错。通过@debug參数，能够在未运行语句（即还不至于报错停止之前）就把须要运行的语句打印出来，注意顺序非常重要，假设在运行报错后你再想打印就不一定能打印出来了。
对于差点儿每行后面都加入的@nl。当然是有意图的。假设不加换行符，代码可能会变成单行非常长的字符串，print出来不直观。
甚至看起来非常痛苦，尽管如今有格式化工具，可是不是每次都破解成功，对单串字符串的美化还是比較浪费时间的。
最后，通过sp_executesql运行SQL字符串，这是一个系统存储过程。须要提供两个固定參数，第一个是SQL字符串，第二个是參数列。这些參数必须是nvarchar类型。
在这个样例中。调用语句在存储过程内部。你也能够在外部调用存储过程。可是须要记住的是动态SQL不能得知不论什么调用參数。
注意存储过程最后的參数列@paramlist，是静态的，也就是參数集是固定的，即使有些參数并非每次都会使用到。
測试：
能够使用以下语句对存储过程进行測试：
EXEC [sp_Get_orders]@salesorderid = 70467 EXEC [sp_Get_orders]@custid = 30097 EXEC [sp_Get_orders]@prodid = 936 EXEC [sp_Get_orders]@prodid = 936, @custname = 'Carol' EXEC [sp_Get_orders]@fromdate = '2007-11-01 00:00:00.000', @todate = '2008-04-18 00:00:00.000' EXEC [sp_Get_orders]@employeestr = '20124,759,1865', @custid = 29688 DECLARE @tbl intlist_tbltype INSERT @tbl(val) VALUES(20124),(759),(1865) EXEC [sp_Get_orders]@employeetbl = @tbl, @custid = 29688对于这类情况，须要对全部參数进行測试。最好是能知道实际使用中哪些參数的使用频率最高。
本文出处：http://blog.csdn.net/dba_huangzj/article/details/50202371
动态SQL的编译和缓存：
每当用户以同样查询參数集进行调用这个存储过程时，运行计划会被重用。假设调用上一章的存储过程sp_get_orders_1时，如：
EXEC sp_get_orders_1@salesorderid = 70467 EXEC sp_get_orders_1@salesorderid = 70468 EXEC sp_get_orders_1@salesorderid = 70469
由于OPTION(RECOMPILE)，所以不缓存不论什么运行计划而且每次都重编译。可是对于本文中的存储过程：
EXEC [sp_Get_orders]@salesorderid = 70467 EXEC [sp_Get_orders]@salesorderid = 70468 EXEC [sp_Get_orders]@salesorderid = 70469
仅仅会针对第一次调用进行编译并缓存运行计划，兴许两次调用将使用第一的运行计划进行直接运行。可是当调用的參数变化时，如：
EXEC [sp_Get_orders]@salesorderid = 70467,@prodid = 870
会发生新的编译并产生新的缓存条目，但原有的用于查询SalesOrderID的运行计划不受影响。
特殊的查询条件：
在上一篇静态SQL中，已经展示了怎样用静态SQL实现某些特殊的查询条件，本部分将演示用动态SQL来完毕这些工作。前面提到过，静态SQL针对简单的查询条件，足以应付自如，可是当需求数量和复杂度逐步添加时。静态SQL将变得不可控。此时就须要考虑动态SQL。
数据分布不均的情况：
在非常多系统中，常见的一类情况是，订单表上有一个状态列Status。里面有4个值：N（新订单）、P（处理中）、E（异常订单）、C（已处理订单）。同一时候差点儿99%的数据都是为C。
这样的情况下能够使用对该列中C值的过滤索引/筛选索引（filterindex）来过滤不必要的数据或须要常常查询的数据。
可是假设在动态SQL中这样写：
IF @status IS NOT NULL SELECT @sql += ' AND o.Status = @status'
由于动态SQL的运行计划是针对全部情况进行优化的，所以这样的写法是不会专门针对过滤索引起效，须要额外制定一些操作逻辑来“指示”优化器使用这个过滤索引，如：
IF @status IS NOT NULL
SELECT @sql += ' AND o.Status = @status' +
CASE WHEN @status <> 'C'
THEN ' AND o.Status <> ''C'''
ELSE ''
END
这样的情况是针对单值參数，假设@status为多值。即用户须要筛选某些类型的数据，则须要按这样的方式加入很多其它的处理逻辑。
自己定义排序：
在动态SQL中，非常常见的应用常见是使用自己定义的排序规则。通过用户前端输入的排序条件进行结果集排序。比方：
@sql += ' ORDER BY ' + @sortcol
这样的写法能够满足多列排序。比方’SalesOrderID, OrderTime Desc’。尽管对于满足功能来说。已经足够了，可是由于client不知道查询本身。可能导致传入的參数不属于相关的表或其它因素导致报错，特别是ORDER BY在T-SQL的逻辑处理中属于接近最后部分。SELECT语句可能把原始列进行重命名、运算等，导致前端无法得知SELECT的终于列名。另外即使是使用了正确的名字，可是在兴许可能由于表结构的变更、列名变更等因素又带来报错。
这样的情况事实上非常难避免。只是多考虑一下问题可能就没有那么严重，比方能够用以下的方式来预处理：
SELECT @sql += ' ORDER BY ' +
CASE @sortcol WHEN 'OrderID' THEN 'o.OrderID'
WHEN 'EmplyoeeID' THEN 'o.EmployeeID'
WHEN 'ProductID' THEN 'od.ProductID'
WHEN 'CustomerName' THEN 'c.CompanyName'
WHEN 'ProductName' THEN 'p.ProductName'
ELSE 'o.OrderID'
END + CASE @isdesc WHEN 0 THEN ' ASC' ELSE ' DESC' END
备用表：
在上一章备用表中。提到了关于不同參数訪问不同表的情况。这样的情况在动态SQL中实现也不难，能够把FROM部分改写成：
ROM dbo.' + CASE @ishistoric
WHEN 0 THEN 'Orders'
WHEN 1 THEN 'HistoricOrders'
END + ' o
JOIN dbo.' + CASE @ishistoric
WHEN 0 THEN '[Order Details]'
WHEN 1 THEN 'HistoricOrderDetails'
END + ' od
可是为了避免SQL注入的风险，不建议通过前端程序传入表名，而是传入某些标识參数然后在存储过程内部进行表名选择。
本文出处：http://blog.csdn.net/dba_huangzj/article/details/50202371
缓存问题：
參数化动态SQL的当中一个优势是能够通过计划重用而降低编译次数。可是缓存并不总是好的。比方在上一章基础技能部分提到的：
1. exec sp_Get_orders_1@fromdate='20050701',@todate ='20050701' 2. exec sp_Get_orders_1@fromdate='20050101',@todate ='20051231'
尽管參数集同样，可是当值不同的时候，假设这些不同的值的数据分布严重不均匀，会导致运行计划无法高效支持全部查询。
这样的情况在动态SQL和静态SQL中都比較常见，以下来介绍一下处理方法：
OPTION(RECOMPILE)：
对，你又见到它了。在上面提到的特定情况下，假设查询条件是@fromdate和@todate，加入OPTION(RECOMPILE)：
IF (@fromdate IS NOT NULL OR @todate IS NOT NULL) SELECT @sql += ' OPTION(RECOMPILE)' + @nl
通常来说，当你发现查询条件上有合适的索引，而且选择度非常依赖于实际值的输入，那么能够加入OPTION(RECOMPILE)，以便你总能通过编译得到关于当前统计信息的最佳运行计划。可是显然这样的方式会加入一部分不必要的编译。比方两次运行的值全然一样时。依然还会编译。
索引提示和其它提示：
有时候能够尝试使用“提示，hints”。能够通过CASE WHEN 推断须要传入什么參数。而且对这些參数额外指定须要走的索引。
可是正如前面提到过的。提示要慎用。特别是索引提示，除非你确保索引名永不变更：
FROM dbo.Orders o ' + CASE WHEN @custid IS NOT NULL AND
(@fromdate IS NOT NULL OR
@todate IS NOT NULL)
THEN 'WITH (INDEX = CustomerID) '
ELSE ''
END
第二种提示是使用OPTIMIZE FOR。假设你希望运行计划总是使用占用最多的情况来编译，比方前面提到的status类型中的C，那么能够加入：
IF @status IS NOT NULL @sql += ' OPTION (OPTIMIZE FOR (@status = ''C''))'
假设你不想优化器通过嗅探參数来产生运行计划。能够使用：
IF @fromdate IS NOT NULL AND @todate IS NOT NULL @sql += ' OPTION (OPTIMIZE FOR (@fromdate UNKNOWN, @todate UNKNOWN))'
这样优化器就不会使用标准假设，即10%左右来编译查询。
本文出处：http://blog.csdn.net/dba_huangzj/article/details/50202371
总结：
动态SQL非常强大。可是假设读者归纳能力比較强的话，能够看到，动态SQL的问题主要是在不能非常好地利用计划缓存或使用的是不合适的运行计划，导致性能问题。
对于这类情况，有非常多方法能够使用。而且假设能够，最好还是考虑非数据库层面的其它技术。
可是我们的目的还是一个：保证运行计划针对不论什么參数，最起码绝大部分參数都是最佳的。而且能够尽可能重用。
最后。须要提醒的是。不论什么技术、技巧。都应该在尽可能贴近实际环境的測试环境中做充分的測试，以便得到你希望的结果。
本文出处：http://blog.csdn.net/dba_huangzj/article/details/50202371