How to set quoted identifier off while creating stored procedure using exec

I have a scenario to alter the columns of few user defined table types programmatically. To do this, I need to drop the referenced stored procedures. So, I have designed my SQL scripts to do following activities:

1. Taking the backup of stored procedures from sys.sql_modules table into a staging table (definition, uses_ansi_nulls and uses_quoted_identifier columns).
2. Dropping the stored procedure.
3. Altering the user defined table types.
4. Now, using the staging table's definition column to recreate the stored procedure, but I am not able to use the values of uses_ansi_nulls and uses_quoted_identifier columns. How can I reuse the uses_ansi_nulls and uses_quoted_identifier column while recreating the stored procedure.

I am using cursor to get the content of staging columns into variables and then using exec() executing the definition of stored procedure like below:

 SET @ProcDefinition_Complete=@uses_ansi_nulls_Definition + CHAR(10)+' GO '+ CHAR(10)+@uses_quoted_identifier_Definition+ CHAR(10)+' GO '+  CHAR(10)+ @ProcDefinition
        
        EXEC (@ReferencingDefinition_Complete)

Above statement gives error:

 Incorrect syntax near 'GO'. 

And when I remove GO statement then it gives the error:

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

You can't do what you're trying to do. Not by iterating over the rows in the table with a cursor and pulling the definitions out into variables to execute and changing ansi_nulls and quoted_identifier according to the values given by each row, because the cursor execution has to live in one batch.

Why does "have to live in one batch" matter? Read on.

set quoted_identifier on;
select @@options & 256; -- will print 256 if qi is on, 0 if it is off

This will print 256. So far so good. But what about this?

set quoted_identifier on;
print @@options & 256;
set quoted_identifier off;
print @@options & 256;

Does this print 256 and then 0? Nope. it prints 0, and the 0 again. Weird! OK, let's make sure quoted_identifier is on by running that in its own batch, and then try turning it off conditionally:

set quoted_identifier on;
go -- note this additional go
if (1 = 0) set quoted_identifier off;
print @@options & 256;

When we start the second batch (the bit after go) the quoted_identifier settings is most certainly on. Then we only turn it off if 1 = 0. Since 1 does not equal 0, quoted_identifier should stay on. So we would expect to print 256.

What do we actually print? 0. What's going on? Let's check the docs:

For a top-level ad-hoc batch parsing begins using the session's current setting for QUOTED_IDENTIFIER. As the batch is parsed any occurrence of SET QUOTED_IDENTIFIER will change the parsing behavior from that point on, and save that setting for the session. So after the batch is parsed and executed, the session's QUOTED_IDENTIFER setting will be set according to the last occurrence of SET QUOTED_IDENTIFIER in the batch.

(Emphasis added)

You can't conditionally change the quoted_identifier setting within a batch. It seems like dynamic SQL can't save you either, because you would need to set quoted_identifier and then create procedure in the same dynamic sql string, and you can't, because one dynamic exec is one batch, and create procedure has to be the first statement in the batch.

But wait, there's more.

declare @cmdOn varchar(max) = 'exec(''set quoted_identifier on; print @@options & 256;'')';
declare @cmdOff varchar(max) = 'exec(''set quoted_identifier off; print @@options & 256;'');';

declare @both varchar(max) = concat(@cmdOn, char(10), @cmdOff);
print @both;
exec (@both);

Just so it's clear what's going on, here's the output of that:

exec('set quoted_identifier on; print @@options & 256;')
exec('set quoted_identifier off; print @@options & 256;');
256
0

So... yay! We've managed to execute a single piece of dynamic sql (the value of @both), and we've changed the setting of quoted_identifier inside that! Cool. What did it cost us? Nested dynamic calls to exec().

Does this save us? Nope.

set quoted_identifier on; -- this is the only line that matters;

declare @qi varchar(max) = 'exec(''set quoted_identifier off;'')'; -- it makes no difference what you put here
declare @def varchar(max) = 'exec(''create or alter procedure p as begin set nocount on end;'');';

declare @both varchar(max) = concat(@qi, char(10), @def);
exec (@both);

select uses_quoted_identifier from sys.sql_modules where object_name(object_id) = 'p';

-- returns 1

The nested calls to exec don't help us, because (from the docs):

For a nested batch using sp_executesql or exec(), the parsing begins using the QUOTED_IDENTIFIER setting of the session. If the nested batch is inside a stored procedure, parsing starts using the QUOTED_IDENTIFIER setting of the stored procedure. As the nested batch is parsed, any occurrence of SET QUOTED_IDENTIFIER will change the parsing behavior from that point on, but the session's QUOTED_IDENTIFIER setting will not be updated.

What can you do instead?

Run your "cursor" outside the procedure creation batch.

For instance, write a small program (or powershell script) which reads the backup definitions, along with the required settings, and then executes each create procedure as its own command.

Or read the contents of the backups and dump it all to a file, including "go" statements for each row in the backups table. The execute the file contents as a script.

Pseudocode for the program based solution:

take backup of sql_modules via ssms/whatever
drop procedures via ssms/whatever
run program
   open connection to sql
   read definition, settings from backup table
   foreach (definition, settings)
      execute sql command ("set ansi_nulls ?; set quoted_identifier ?");
      execute sql command (definition)
   close connection
exit program