T-SQL动态查询（4）——动态SQL

接上文：T-SQL动态查询（3）——静态SQL

 

前言：

前面说了很多关于动态查询的内容，本文将介绍使用动态SQL解决动态查询的一些方法。

 

为什么使用动态SQL：

在很多项目中，动态SQL被广泛使用甚至滥用，很多时候，动态SQL又确实是解决很多需求的首选方法。但是如果不合理地使用，会导致性能问题及无法维护。动态SQL尤其自己的优缺点，是否使用需要进行评估分析：

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

动态SQL优点：

• 动态SQL提供了强大的扩展功能，能够应付复杂的需求，即使在需求增加时也能应对，并且不会因为需求的增加而导致代码的线性增长。
• 执行计划可以缓存查询字符串，意味着大部分查询条件可以重用执行计划缓存而不会导致不必要的重编译。

动态SQL缺点：

• 不合理的编码会导致代码的维护陷入困境。
• 动态SQL是用于应对较高级的问题，对于简单问题，会变得大材小用。
•  动态SQL的测试显然比其他代码困难，特别是对最终执行的语句的获取，同时容易因为编码的不规范导致语法错误。
• 相对于前面的OPTION(RECOMPILE)，动态SQL需要加入对权限控制的考虑。
• 动态SQL的计划缓存并不总是你想象的那样，有时候因为输入的参数值而导致不同的计划生成。

静态SQL其实可以应对大部分的日常需求，但是随着需求的增加，静态SQL会变得越来越复杂，同时可能带来过多的重编译，此时应该考虑动态SQL。

 

动态SQL简介：

概述：

在SQL Server中，动态SQL可以由三种方式实现：

1. T-SQL存储过程
2. CLR存储过程
3. 客户端语句

本文着重介绍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有自己的权限范围。

如果在客户端程序或CLR存储过程中创建动态SQL，还需要额外授予用户具有查询中涉及到的表、视图、自定义函数上的SELECT权限。根据客户端程序和CLR存储过程的不同，权限链可能会非常混乱和失控。但是可以使用下面两种方式来应付：

1. 创建一个证书，对存储过程使用这个证书进行签名。然后使用证书创建一个用户，并由于用户所需的SELECT权限。
2. 在存储过程中添加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