创建/修改具有不同 QUOTED_IDENTIFIER 值的多个对象(在 sproc 内) [英] Create/Amend multiple objects (within a sproc) with different QUOTED_IDENTIFIER values

问题描述

作为开发辅助工具，我正在编写一个存储过程，用于将数据库对象从一个数据库创建/修改为另一个数据库.(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 内)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！