主页 / dba / 问题 / 157912
Accepted
gareth
Asked: 2016-12-13 07:50:04 +0800 CST

为什么我不能像我想象的那样在 T-SQL 中使用变量？

  • 772

原谅我，我是一名已经转向 SQL 世界的开发人员。我认为我可以通过添加变量来改进一些 SQL，但它并没有像我预期的那样运行。有人能告诉我为什么这不起作用吗？我不想解决问题，我想知道为什么这不起作用的原因，因为我相信它应该有一个很好的理由，但目前它并没有在我身上跳出来。

DECLARE @DatabaseName varchar(150)
SET @DatabaseName = 'MyAmazingDatabaseName'

CREATE DATABASE @DatabaseName
GO

USE @DatabaseName
GO
sql-server-2008 t-sql

4 个回答

  1. Best Answer

    根据变量的在线书籍页面

    变量只能在表达式中使用，不能代替对象名称或关键字。要构造动态 SQL 语句，请使用 EXECUTE。

    例如，如果您在 where 子句中使用变量，它将按照您期望的方式工作。至于为什么，我认为这与解析器无法评估变量并因此检查是否存在有关。执行时，首先解析查询的语法和对象，然后，如果解析成功，则在设置变量的位置执行查询。

    DECLARE @name varchar(20);
SET @name = 'test';

CREATE TABLE [#tmp]([val] varchar(10));

insert into #tmp
values('test')

SELECT *
FROM [#tmp]
WHERE [val] = @name;
    • 21

  2. SQL 语句中变量使用的限制源于 SQL 的体系结构。

    SQL 语句的处理分为三个阶段：

    1. 准备 - 解析语句并编译执行计划，指定访问哪些数据库对象、如何访问以及如何关联。执行计划保存在计划缓存中。
    2. 绑定 - 语句中的任何变量都替换为实际值。
    3. 执行 - 使用绑定值执行缓存的计划。

    SQL Server 对程序员隐藏了准备步骤，并且执行起来比 Oracle 和 DB2 等更传统的数据库要快得多。出于性能原因，SQL 可能会花费大量时间来确定最佳执行计划，但仅在重新启动后第一次遇到语句时才会这样做。

    所以在静态 SQL中，变量只能用在不会使执行计划失效的地方，所以不能用于表名、列名（包括 WHERE 条件下的列名）等。

    动态 SQL存在于无法绕过限制的情况，程序员知道执行时间会稍长一些。动态 SQL 很容易受到恶意代码注入的攻击，所以要小心！

    • 18

  3. 如您所见，“为什么”问题需要不同类型的答案，包括语言的历史基本原理和基本假设，我不确定我是否真的能做到这一点。

    SQL MVP Erland Sommarskog 的这篇综合文章确实试图提供一些基本原理以及机制：

    动态 SQL 的祸与福：

    缓存查询计划

    您在 SQL Server 中运行的每个查询都需要一个查询计划。当您第一次运行查询时，SQL Server 会为它构建一个查询计划——或者用术语来说——它会编译查询。SQL Server 将计划保存在缓存中，下次运行查询时，计划将被重用。

    这（以及安全性，见下文）可能是最大的原因。

    SQL 的运行前提是查询不是一次性操作，而是会被反复使用。如果表（或数据库！）实际上没有在查询中指定，则无法生成并保存执行计划以供将来使用。

    是的，并不是我们运行的每一个查询都会被重用，但这是 SQL 的默认操作前提，所以“异常”意味着异常。

    Erland 列出了其他一些原因（请注意，他明确列出了使用存储过程的优点，但其中许多也是参数化（非动态）查询的优点）：

    • 权限系统：如果 SQL 引擎不知道您将要操作的表（或数据库），它就无法预测您是否有权运行查询。使用动态 SQL 的“权限链”很麻烦。
    • 减少网络流量：通过网络传递存储过程的名称和一些参数值比长查询语句要短。
    • 封装逻辑：您应该熟悉从其他编程环境封装逻辑的优势。
    • 跟踪使用的内容：如果我需要更改列定义，如何找到调用它的所有代码？存在系统过程以查找 SQL 数据库中的依赖关系，但前提是代码位于存储过程中。
    • 易于编写 SQL 代码：创建或修改存储过程时会进行语法检查，因此希望减少错误。
    • 解决错误和问题：与不断变化的动态 SQL 相比，DBA 可以更轻松地跟踪和测量单个存储过程的性能。

    同样，这些中的每一个都有一百个细微差别，我不会在这里讨论。

    • 7

  4. 你需要使用动态sql

    DECLARE @DatabaseName varchar(150) = 'dbamaint'
declare @sqltext nvarchar(max) = N''

set @sqltext = N'CREATE DATABASE '+quotename(@DatabaseName)+ ';'

print @sqltext 

-- once you are happy .. uncomment below
--exec sp_executesql @sqltext
set @sqltext = ''
set @sqltext = N'use '+quotename(@DatabaseName)+ ';'
print @sqltext 
-- once you are happy .. uncomment below
--exec sp_executesql @sqltext

    下面是 print 的输出 .. 一旦你取消注释exec sp_executesql @sqltext语句将被实际执行...

    CREATE DATABASE [dbamaint];
use [dbamaint];
    • 2

相关问题