问题描述
作为开发辅助工具，我正在编写一个存储过程，用于将数据库对象从一个数据库创建/修改为另一个数据库.(sproc 采用对象名称的 CSV 字符串，它使用 XML 将其拆分为单个值，因此需要打开 QUOTED_IDENTIFIER 才能运行 sproc.)
As a development aid, I am writing a stored procedure which creates/amends database objects from one database into another one. (The sproc takes a CSV string of object names, which it splits into individual values using XML, and therefore the QUOTED_IDENTIFIER needs to be turned on for the sproc to run.)
但是，正在创建/修改的对象包括可以打开或关闭 QUOTED_IDENTIFIER 的存储过程.
However, the objects being created/amended include stored procedures where QUOTED_IDENTIFIER could be turned either on or off.
根据对这个非常相似的问题的回答(其中专门讨论了创建单个存储过程)如果您在另一个存储过程中创建/修改一个存储过程，它将总是使用 QUOTED_IDENTIFIER 设置在 "父母"过程.
According to the answer to this very similar question (which talks specifically about creating a single stored procedure) if you create/amend a stored procedure within another stored procedure, it will always use the QUOTED_IDENTIFIER values set in the "parent" sproc.
有人知道在创建/修改多个存储过程时能够设置不同的 QUOTED_IDENTIFIER 标志值的方法吗?
Does anybody know of a way to be able to set different QUOTED_IDENTIFIER flag values when creating/amending multiple stored procedures?
我在循环中尝试了非常简单的代码(例如以下)，但正如上面的答案所暗示的那样，这对结果没有影响，并且创建/修改的 sproc 总是设置 QUOTED_IDENTIFIER到 ON...
I've tried very simple code within the loop (such as the following) but as the above answer suggests, this has no effect on the result and the created/amended sproc always has QUOTED_IDENTIFIER set to ON...
IF @QUOTED = 1 -- from sys.sql_modules.uses_quoted_identifier
SET QUOTED_IDENTIFIER ON
ELSE
SET QUOTED_IDENTIFIER OFF
EXEC sp_executesql @DEFINITION -- from sys.sql_modules.definition
推荐答案
非常感谢 @Jeroen向我发送了解决方案的路径，即创建另一个开发辅助存储过程，并将 QUOTED_IDENTIFIER 设置为 OFF.
With many thanks to @Jeroen who sent me down the path to the solution, which is to create another development-aid stored procedure with QUOTED_IDENTIFIER set to OFF.
在主要开发辅助 sproc 的主循环中，它通过自身(如果需要 ON)或通过辅助 sproc(如果需要 OFF)执行定义).
Within the main loop of the primary development-aid sproc it executes the definition through itself (if ON is required) or through the secondary sproc (if OFF is required).
这是我现在工作的非常简化的伪版本...
This is a very simplified pseudo version of what I now have working...
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE DEV_AID_SUB
@DEFINITION NVARCHAR(MAX)
AS
EXEC sp_executesql @DEFINITION
---
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE DEV_AID
AS
BEGIN
WHILE @LOOP = 1
IF @QUOTED = 1
EXEC sp_executesql @DEFINITION
ELSE
EXEC DEV_AID_SUB @DEFINITION
END
END
这篇关于创建/修改具有不同 QUOTED_IDENTIFIER 值的多个对象(在 sproc 内)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！