EXEC和设置Quoted_Identifier [英] EXEC and Set Quoted_Identifier

问题描述

我有一个存储过程[A]，它创建了另一个存储过程[B]

I've got a Stored proc [A] that creates another stored proc [B]

[A]永远不会由最终用户运行，并且没有参数或其他不受信任的数据.相反，我只是用它来自动执行复杂SP的创建[B]. [A]除非内部结构发生更改，否则总会有相同的结果.因此，我认为这是安全的.

[A] Will never be run by end users and has no parameters or other untrusted data. Instead it is used by me simply to automate the create of the complex SP [B]. [A] Will always have the same result unless it's internals are changed. Therefore I consider this to be safe.

[B]由于使用xml，因此需要打开Quoted_Identifiers.

[B] requires Quoted_Identifiers ON as it uses xml.

如果我复制并粘贴生成的SP，它可以正常工作，但是如果我让[A]用EXEC创建它，则SP在运行时会失败.

If I copy and paste the generated SP it works fine but if I let [A] create it with EXEC then the SP fails when running.

我尝试在[B]中添加SET QUOTED_IDENTIFIERS ON，但效果不明显.

I've tried adding SET QUOTED_IDENTIFIERS ON inside [B] but it has no noticeable effect.

如果我使用sp_ExecuteSQL，也会发生相同的问题 我也曾尝试在调用[B]之前将其设置为打开，但这似乎没有任何效果(但是为什么在始终处于打开状态的情况下却如此)

The same issue also occurs if I use sp_ExecuteSQL I've also tried setting it on before calling [B] but that also seems to have no effect (but why would it in a context where it was always on)

我的猜测是EXEC和sp_ExecuteSQL始终使用设置OFF，而SET命令由解析器而不是SQL引擎本身处理.那么如何使EXEC正确创建proc?

My guess is that EXEC and sp_ExecuteSQL always use the setting OFF and the SET command is processed by the parser rather than the SQL engine itself. So how can I make EXEC create the proc correctly?

推荐答案

您需要 QUOTED_IDENTIFIER 为ON，其中创建了存储过程A.注意:

You need QUOTED_IDENTIFIER to be ON where stored procedure A is created. Note:

创建存储过程时，将捕获SET QUOTED_IDENTIFIER和SET ANSI_NULLS设置并将其用于该存储过程的后续调用.

When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

这意味着，任何创建存储过程的存储过程都将传递其自身创建过程中有效的设置.例如:

Which, by implication, means that any stored procedure that creates stored procedures will pass on the settings that were in force during its own creation. E.g.:

set quoted_identifier on
go
create procedure ABC
as
    exec('create procedure DEF as')
go
set quoted_identifier off
go
exec ABC
go
select definition,uses_quoted_identifier from sys.sql_modules
where object_id=OBJECT_ID('DEF')

产生:

definition                             uses_quoted_identifier
-------------------------------------- ----------------------
create procedure DEF as                1

这篇关于EXEC和设置Quoted_Identifier的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！