SQL Server-聚焦深入理解动态SQL查询

前言

之前有园友一直关注着我快点出SQL Server性能优化系列，博主我也对性能优化系列也有点小期待，本来打算利用周末写死锁以及避免死锁系列的接着进入SQL Server优化系列，但是在工作中长时间都是利用EF来操作SQL，不免对写SQL语句有些生疏，在某些场景下还是只能利用底层的SQL语句或者写存储过程来实现，很久没写存储过程都忘记怎么写了，所以本节穿插动态SQL查询的文章，别着急，博主说过不会烂尾，博主再忙也会抽空将整个SQL Server系列梳理完毕，那样的话，无论对初级还是中级者都可以从中受益匪浅，至少我是这么认为，呵呵。

动态SQL语句查询

前前篇我们简短叙述了利用EXEC和EXECUTE来进行动态SQL语句查询，并未深入去讲解，借博主工作中重新回到写原生SQL语句的机会，我们再来回顾下动态SQL语句查询。既然是动态SQL查询，说明在某些场景下利用硬编码SQL语句查询的方式是不可行的，比如查询条件的不固定，这是最常见的情景，那么动态SQL语句查询有哪几种方式呢？万变不离其宗，只有以下三种方式，请往下看。

参数化SQL语句动态查询

参数化SQL查询是动态SQL查询中最简单的一种，因为我们只需要传递参数即可，查询条件是固定的，我们一起来温故而知新。

USE AdventureWorks2012

DECLARE @AccountNumber AS VARCHAR(200)

SET @AccountNumber = 'AW00000002'

SELECT 
    StoreID, CustomerID, ModifiedDate, PersonID
FROM Sales.Customer
WHERE AccountNumber = @AccountNumber

EXEC动态SQL语句动态查询 

这个相对来说比上一个要略微复杂一点，对于复杂的SQL语句拼接，我们来看看。

USE TSQL2012

DECLARE @shipcity AS VARCHAR(50)
DECLARE @sqlCommand AS VARCHAR(500)
DECLARE @columnList AS VARCHAR(200)

SET @shipcity = 'Lyon'
SET @columnList = 'orderid, custid, orderdate, shipname, shipaddress, shipcity'
SET @sqlCommand = 'SELECT '+ @columnList + ' FROM Sales.Orders WHERE shipcity = '+ @shipcity

EXEC(@sqlCommand)

 

居然出错了，让人始料未及，当设置参数值时我们应该将 SET @shipcity = 'Lyon' 进行如下修改：

SET @shipcity = '''Lyon'''

sp_executesql动态SQL语句查询（推荐）

USE TSQL2012

DECLARE @shipcity AS VARCHAR(50)
DECLARE @sqlCommand AS VARCHAR(500)
DECLARE @columnList AS VARCHAR(200)

SET @shipcity = 'Lyon'
SET @columnList = 'orderid, custid, orderdate, shipname, shipaddress, shipcity'
SET @sqlCommand = 'SELECT '+ @columnList + ' FROM Sales.Orders WHERE shipcity = @shipcity'

EXECUTE sp_executesql @sqlCommand, 
                      N'@shipcity VARCHAR(50)', 
                      @shipcity = @shipcity

之前从未遇到过这种情况，查此错误居然发现要：sp_executesql执行的SQL必须定义为NVARCHAR类型即必须定义为UNICODE，这里算是学习了。

对于利用sp_executesql来执行动态sql语句查询的方式作为推荐最主要是因为其在查询执行计划中，无论其变量值是否改变查询计划都会进行重用，当然还有其他好处，比如利用EXEC来执行查询，此时进行拼接非常容易出错。同时在写动态SQL时个人一般推崇将查询的列作为一个列表进行定义，对于参数也进行定义，这样可维护性强不至于看起来乱糟糟的利于后续排查问题。

讲到这里是不是叙述完毕了呢，那就太没意思了，相信看过博主所写的内容一般都是由浅入深，下面我们继续往下看。

 一直讲的动态SQL语句查询，你难道就没有怀疑过仅仅只能进行查询，难道不能创建表或者创建视图么，答案当然是可以的。我们简单看下创建表。

Declare @SQL VarChar(1000)
DECLARE @TableName AS VARCHAR(10)

SET @TableName = 'Test'

SELECT @SQL = 'Create Table ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'

Exec (@SQL)

利用EXEC或者sp_executesql居然还能创建表，还能最高级一点么，答案当然是可以，请继续往下看。

当我们在不是当前打开会话中的数据库中去创建另外数据库的视图时可行不可行呢？

Create View AdventureWorks2012.dbo.Auths AS     (SELECT             StoreID, CustomerID, ModifiedDate, PersonID    FROM Sales.Customerl    )

此时创建视图你会发现不能正常创建视图，说明创建视图必须是在当前会话指定的数据库中才可以。

但是利用sp_executesql就能解决跨数据库创建视图的问题，如下：

DECLARE @SQL NVarChar(1000)

SELECT @SQL = 'Create View Auths AS (SELECT CustomerID, ModifiedDate FROM Sales.Customer)'

EXECUTE AdventureWorks2012.dbo.sp_executesql @sql

[object Object]

此时你会发现利用sp_executesql不仅可以重用查询执行计划并且可以跨数据库创建视图，那么它难道就没有限制么，答案当然是有的，请继续往下看。

存储过程执行动态SQL语句查询

当在存储过程中执行动态SQL语句查询时，此时动态SQL语句是在当前用户的权限下进行而不是调用存储过程的用户的权限，换句话说如果我们对存储过程上的表没有权限，那么运行存储过程将出现问题。还是不理解是什么意思么，也就是说存储过程运行是有其作用域的。我们举一个例子，我们可以利用如下语句来设置查询的行数。

SET ROWCOUNT 3

所以接下来我们利用上述语句来查询返回的行数。

EXEC('SET ROWCOUNT 3')SELECT * FROM Sales.CustomersEXEC('SET ROWCOUNT 0')

从上我们可以发现我们设置限制返回的行数根本不起作用，这是因为设置返回的行数已经超出了查询的作用域。所以我们必须将其设置限制行数放到EXEC中，如下：

EXEC('SET ROWCOUNT 3 SELECT * FROM Sales.Customers SET ROWCOUNT 0')

WHERE 1 = 1问题 

对于不确定多条件筛选利用WHERE 1 = 1是否会带来性能问题，但是除此之外似乎没有什么好的办法比利用WHERE 1 = 1来减少条件的判断从而加长SQL语句，可能会导致性能的下降。下面我们来看一个简单的例子

USE  AdventureWorks2012
GO
IF  EXISTS (SELECT * FROM sys.objects 
            WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [Sales].[GetSalesOrders]
GO

CREATE PROCEDURE [Sales].[GetSalesOrders] (
    @CustomerID INT = NULL,
    @CreditCardID INT = NULL)
AS
SET NOCOUNT ON; 

DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);

SELECT    @ParameterDefinition = '
    @CustomerParameter INT,
    @CreditCardIDParameter INT
';

SELECT    @SQL = N'
SELECT    [SalesOrderID], [OrderDate], [Status], 
    [CustomerID], [CreditCardID]
FROM    [Sales].[SalesOrderHeader]
WHERE 1 = 1
';

IF @CustomerID IS NOT NULL
    SELECT @SQL = @SQL + N'
    AND CustomerID = @CustomerParameter ';
    
IF @CreditCardID IS NOT NULL
    SELECT @SQL = @SQL + N'
    AND CreditCardID = @CreditCardIDParameter ';
    
    
EXECUTE sp_executesql
    @SQL,
    @ParameterDefinition,
    @CustomerParameter = @CustomerID,
    @CreditCardIDParameter = @CreditCardID;    
GO

SET NOCOUNT OFF;

再复杂也不过就是多表查询和多条件筛选罢了，还是比较简单。得出如下结果。

上述利用存储过程创建动态SQL语句没什么毛病。

那么问题来了，要是如果我们想调试创建过程生成的SQL语句是否有没有错误，我们此时该如何做呢？为了利于调试我们可以将上述修改如下，添加一个debug标识来打印SQL。

USE  AdventureWorks2012
GO
IF  EXISTS (SELECT * FROM sys.objects 
            WHERE object_id = OBJECT_ID(N'[Sales].[GetSalesOrders]') 
            AND type in (N'P', N'PC'))
DROP PROCEDURE [Sales].[GetSalesOrders]
GO

CREATE PROCEDURE [Sales].[GetSalesOrders] (
    @CustomerID INT = NULL,
    @CreditCardID INT = NULL,
    @debug bit = 0)
AS
SET NOCOUNT ON; 

DECLARE @SQL NVARCHAR(4000);
DECLARE @ParameterDefinition NVARCHAR(4000);

SELECT    @ParameterDefinition = '
    @CustomerParameter INT,
    @CreditCardIDParameter INT
';

SELECT    @SQL = N'
SELECT    [SalesOrderID], [OrderDate], [Status], 
    [CustomerID], [CreditCardID]
FROM    [Sales].[SalesOrderHeader]
WHERE 1 = 1
';

IF @CustomerID IS NOT NULL
    SELECT @SQL = @SQL + N'
    AND CustomerID = @CustomerParameter ';
    
IF @CreditCardID IS NOT NULL
    SELECT @SQL = @SQL + N'
    AND CreditCardID = @CreditCardIDParameter ';
    
IF @debug = 1
    PRINT @SQL

EXECUTE sp_executesql
    @SQL,
    @ParameterDefinition,
    @CustomerParameter = @CustomerID,
    @CreditCardIDParameter = @CreditCardID;    
GO

EXEC [Sales].[GetSalesOrders] @debug = 1, @CustomerID = 29565

SET NOCOUNT OFF;

此时我们调试发现SQL语句没有写错并且生成的结果如下：

同时我们在消息中可以看到生成的SQL语句如下，这样能够准确无语的保证所写动态SQL没有问题。

总结

本节我们阐述了动态SQL以及要注意的地方，同时为了便于调试保证所生成的SQL语句不会出现问题给出的建议。下节我们开始讲死锁问题，欢迎大家持续关注博客和公众号，对于.NET Core也会持续更新。See u!