SQL Server-聚焦深入理解动态SQL查询(三十二)

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

前言

之前有园友一直关注着我快点出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

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

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

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

SET ROWCOUNT 3

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

EXEC('SET ROWCOUNT 3')

SELECT * FROM Sales.Customers

EXEC('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!





本文转自Jeffcky博客园博客,原文链接:http://www.cnblogs.com/CreateMyself/p/6411676.html,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
25天前
|
SQL 存储 数据挖掘
SQL Server 日期格式查询详解
SQL Server 日期格式查询详解
113 2
|
25天前
|
SQL 数据库
执行 Transact-SQL 语句或批处理时发生了异常。 (Microsoft.SqlServer.ConnectionInfo)之解决方案
执行 Transact-SQL 语句或批处理时发生了异常。 (Microsoft.SqlServer.ConnectionInfo)之解决方案
137 0
|
3月前
|
SQL Java 数据库
jsp中使用Servlet查询SQLSERVER数据库中的表的信息,并且打印在屏幕上
该博客文章介绍了在JSP应用中使用Servlet查询SQL Server数据库的表信息,并通过JavaBean封装图书信息,将查询结果展示在Web页面上的方法。
jsp中使用Servlet查询SQLSERVER数据库中的表的信息,并且打印在屏幕上
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
3月前
|
SQL 运维 监控
SQL Server 运维常用sql语句(二)
SQL Server 运维常用sql语句(二)
31 3
|
3月前
|
SQL XML 运维
SQL Server 运维常用sql语句(三)
SQL Server 运维常用sql语句(三)
20 1
|
3月前
|
SQL 关系型数据库 MySQL
SQL数据库和 SQLserver数据库
【8月更文挑战第19天】SQL数据库和 SQLserver数据库
56 2
|
3月前
|
SQL Java 数据库连接
JDBC连接SQL Server2008 完成增加、删除、查询、修改等基本信息基本格式及示例代码
这篇文章提供了使用JDBC连接SQL Server 2008数据库进行增加、删除、查询和修改操作的基本步骤和示例代码。
|
3月前
|
Java 应用服务中间件 Maven
从零到英雄:一步步构建你的首个 JSF 应用程序,揭开 JavaServer Faces 的神秘面纱
【8月更文挑战第31天】JavaServer Faces (JSF) 是一种强大的 Java EE 标准,用于构建企业级 Web 应用。它提供了丰富的组件库和声明式页面描述语言 Facelets,便于开发者快速开发功能完善且易于维护的 Web 应用。本文将指导你从零开始构建一个简单的 JSF 应用,包括环境搭建、依赖配置、Managed Bean 编写及 Facelets 页面设计。
87 0
|
3月前
|
SQL 存储 安全
Play Framework的安全面纱:揭开隐藏在优雅代码下的威胁
【8月更文挑战第31天】Play Framework 是一款高效、轻量级的 Web 开发框架,内置多种安全特性,助力开发者构建安全稳定的应用。本文详细介绍 Play 如何防范 SQL 注入、XSS 攻击、CSRF 攻击,并提供安全的密码存储方法及权限管理策略,通过具体示例代码展示实施步骤,助您有效抵御常见威胁。
60 0