sql - EXEC and Set Quoted_Identifier

Question

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

[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] requires Quoted_Identifiers ON as it uses xml.

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.

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

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)

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?

Answer 1

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

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')

produces:

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