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.)
However, the objects being created/amended include stored procedures where QUOTED_IDENTIFIER could be turned either on or off.
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.
Does anybody know of a way to be able to set different QUOTED_IDENTIFIER flag values when creating/amending multiple stored procedures?
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
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.
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