主页 / dba / 问题 / 43262
Accepted
ErikE
Asked: 2013-05-29 10:03:10 +0800 CST

sp_executesql 将语句添加到执行的动态脚本？

  • 772

问题：

据我所知，sp_executesql将语句添加到提交的动态 SQL 脚本的开头。但是，SQL Profiler 跟踪不会捕获额外的语句，DBCC OUTPUTBUFFER. 所以：

  1. 有什么方法可以查看添加到提交的动态 SQL 批处理中的额外语句sp_executesql？
  2. 任何人都可以明确确认我对额外陈述的结论是正确/不正确的吗？

背景

我有一个数据库，其中一些对象（视图、同义词、SP）是根据Script表中的数据重写的。如果将数据库移动到另一台服务器，则存储过程会循环遍历Script表的行，将提供的 SQL 脚本中的某些键值替换为为新服务器上下文定义的键值，然后运行该脚本。

一切正常，直到我通过相同的机制进行了一些调整以添加对脚本权限的支持。该数据库与供应商的产品集成，并且在每个环境中，供应商的数据库可以有不同的用户，为了报告目的，必须授予该用户访问我数据库中特定视图的权限。因此，我必须查询该用户（从供应商的数据库中），然后使用该名称在我的数据库中创建该用户（如果它不存在）并最终授予SELECT权限。这需要更冗长的脚本编写并在动态 sql 中执行动态 sql，因此我想传入外部脚本的@Debug参数，以便在尝试执行之前查看正在生成的额外脚本并确认其正确性。

除了更改可以编写脚本的对象类型并使DROP脚本可选之外，我为适应@Debug参数所做的唯一重大更改是更改此：

EXEC (@CreateSQL);

对此：

EXEC sp_executesql @CreateSQL, N'@Debug bit', @Debug;

然后我遇到了一个问题：Script无法再创建表中的一个存储过程，尽管DROP之前它仍然可以正常工作。我得到的结果是这样的：

消息 156，第 15 级，状态 1，第 1 行

关键字“PROCEDURE”附近的语法不正确。

这很混乱，但折腾了半天，终于搞清楚了问题：在执行前sp_executesql偷偷加一条语句，把参数绑定到动态SQL上。DECLARE由于CREATE PROCEDUREmust 是批处理中唯一的语句，但是现在该CREATE PROCEDURE行之前有一个额外的语句，因此会引发错误。它确实说Line 1——这进一步误导了我——但这显然是由引擎调整的，所以人们在处理错误时不会对自己脚本的行号感到困惑。

该问题的解决方案是检测正在使用哪种类型的对象并且不传递@Debug参数，因此必须没有其他语句的脚本可以正常工作。一个快速的改变完成了这项工作：

IF @ScriptType IN ('Procedure', 'View', 'Function') BEGIN
   EXEC sp_executesql @CreateSQL;
END
ELSE BEGIN
   EXEC sp_executesql @CreateSQL, N'@Debug bit', @Debug;
END;

我也可以将我的动态 SQL 嵌套更深一层，以在动态 sql 中创建过程（同样，在表中的脚本中），但在我的情况下，这是一个不太理想的解决方案。

我怀疑使用OUTPUT变量 withsp_executesql还会在脚本末尾添加一个或多个语句，以使引擎能够捕获它们，很可能是在一个SELECT被默默吞下的语句中。

sql-server dynamic-sql

2 个回答

  1. 在 sp_executesql 之前，您应该在 Profiler 中看到一条 sp_prepare 语句，该语句可以提供更多见解： http: //msdn.microsoft.com/en-us/library/ff848808 (v=sql.110).aspx

    有时这可能很难找到，但在 2008R2 中通过扩展事件变得更容易了 - 特别是因果关系跟踪。

    http://msdn.microsoft.com/en-us/library/bb630284.aspx

    • 4
  2. Best Answer

    我发现服务器并没有将注入的脚本内容放入批处理中，而是放入缓存的执行计划中。因此，如果执行的动态 SQL 不在缓存中，您只能在探查器中看到这种情况。这最初让我失望，因为我只在运行一次测试后才开始跟踪，所以该语句已经在缓存中。当我切换数据库服务器并在第一次执行时运行跟踪时，我终于发现了这个事件。

    这是我运行的批次：

    DECLARE @SQL nvarchar(4000), @Debug bit;
SET @SQL = 'WAITFOR DELAY ''00:00:20'';';
EXEC sp_executesql @SQL, N'@Debug bit OUTPUT', @Debug OUTPUT;

    这是我在SQL Profiler 10.0.1600.22对抗中发现的Microsoft SQL Server 2008 R2 (SP2) - 10.50.4263.0 (X64)：

    Event: SP:CacheInsert
Submitted SQL text: WAITFOR DELAY '00:00:20';
Text placed in cache: (@Debug bit OUTPUT)WAITFOR DELAY '00:00:20';

    所以我说有文本被注入是对的，但在几点上是错误的：

    1. 这不是一个正常的DECLARE说法。这是另一回事。那是什么……敬请期待。

    2. 当有 OUTPUT 变量时，它更像是一个带有OUTPUT变量的存储过程。没有SELECT像数据库提供程序驱动程序通常对提交的批处理以收集返回值或行数所做的那样添加隐藏或其他内容。

    3. 没有进行行号调整。额外的脚本被添加到开头，没有换行符。

    如果您尝试直接提交完整的缓存文本，则会收到错误消息：

    (@Debug bit OUTPUT)WAITFOR DELAY '00:00:20';

    消息 1050，第 15 级，状态 1，第 1 行

    此语法仅允许用于参数化查询。

    因此，正确的术语似乎是“参数化查询”。虽然该术语通常指的是前端编码，但显然有一种内部 SQL Server 类型用于在执行计划中缓存的 SQL。

    • 2

相关问题