0

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
freefaller
  • 19,368
  • 7
  • 57
  • 87
  • My idea would be to write another sproc to do `sp_executesql` for you, this one with `QUOTED_IDENTIFIER` explicitly set to `OFF`, and call it when you need to. – Jeroen Mostert Jun 14 '21 at 15:56
  • Thanks @Jeroen - the issue there is that I'm processing the CSV of object names using XML, and therefore I need it set to `ON`. It would also mean more time in having to work out which sprocs were `ON` and which were `OFF` to allocate to the appropriate development-aid sproc. In an "ideal world" I have a single dev-aid sproc that I throw all the object names at, and it copes (so I don't have to!) – freefaller Jun 14 '21 at 15:57
  • @Jeroen - of course, what I **could** have is three sprocs... the primary one, which then calls one of two secondary ones depending on if it needs `ON` or `OFF`. I'll get back to you – freefaller Jun 14 '21 at 16:01
  • Yes, I understand the "master" sproc needs it `ON`, but you will not get around having to query what the setting for a stored procedure is now (your `@QUOTED` above) so if you do, you can do `IF @QUOTED = 0 EXEC MyCreatingProcedureThatHasQIOff @Definition`, with `MyCreatingProcedureThatHasQIOff` just a really stupid proxy for an `EXEC` command. – Jeroen Mostert Jun 14 '21 at 16:01
  • @Jeroen - bingo - that was it... thank you for sending me down that path. If you'd like the credit, please write an answer, otherwise I'll write one myself explaining the creation of 3 sprocs (1 primary, 2 sub) – freefaller Jun 14 '21 at 16:15
  • You technically need only the one sub, since the master will always be `ON` itself. Though you might do that for symmetry purposes, I suppose. – Jeroen Mostert Jun 14 '21 at 16:17
  • @Jeroen - an excellent point! Thanks again – freefaller Jun 14 '21 at 16:18

1 Answers1

0

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
freefaller
  • 19,368
  • 7
  • 57
  • 87