Как отключить цитируемый идентификатор при создании хранимой процедуры с помощью exec

Question

У меня есть сценарий для программного изменения столбцов нескольких типов таблиц, определенных пользователем. Для этого мне нужно отбросить указанные хранимые процедуры. Итак, я разработал свои сценарии SQL для выполнения следующих действий:

1. Создание резервной копии хранимых процедур из таблицы sys.sql_modules в промежуточную таблицу (определение, столбцы uses_ansi_nulls и uses_quoted_identifier).
2. Удаление хранимой процедуры.
3. Изменение типов таблиц, определенных пользователем.
4. Теперь я использую столбец определения промежуточной таблицы для воссоздания хранимой процедуры, но я не могу использовать значения столбцов uses_ansi_nulls и uses_quoted_identifier. Как я могу повторно использовать столбцы uses_ansi_nulls и uses_quoted_identifier при воссоздании хранимой процедуры.

Я использую курсор, чтобы преобразовать содержимое промежуточных столбцов в переменные, а затем с помощью exec() выполняю определение хранимой процедуры, как показано ниже:

       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)

Вышеуказанное утверждение дает ошибку:

       Incorrect syntax near 'GO'. 

И когда я удаляю оператор GO, он выдает ошибку:

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

Answer 1

Вы не можете делать то, что пытаетесь сделать. Не путем итерации по строкам в таблице с помощью курсора и извлечения определений в переменные для выполнения и изменения ansi_nulls и в соответствии со значениями, указанными в каждой строке, потому что выполнение курсора должно выполняться в одном пакете.

Почему важно «жить одной партией»? Читать дальше.

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

Это напечатает. Все идет нормально. Но как насчет этого?

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

Разве эта печать а потом? Неа. он печатает, и снова. Странный! ОК, давай убедимся on запустив его в отдельном пакете, а затем попробуйте условно отключить его:

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

Когда мы запускаем вторую партию (бит после go) настройки наверняка включены. Тогда мы только выключаем его if 1 = 0. Поскольку 1 не равно 0, должен оставаться включенным. Итак, мы ожидаем напечатать 256.

Что мы на самом деле печатаем? 0. В чем дело? Давайте проверим документы:

Для специального пакетного анализа верхнего уровня начинается с использования текущего параметра сеанса для QUOTED_IDENTIFIER. Когда пакет анализируется, любое появление SET QUOTED_IDENTIFIER изменит поведение синтаксического анализа с этого момента и сохранит этот параметр для сеанса. Таким образом, после анализа и выполнения пакета параметр сеанса QUOTED_IDENTIFER будет установлен в соответствии с последним вхождением SET QUOTED_IDENTIFIER в пакете.

(Курсив добавлен)

Вы не можете условно изменить настройку в пакете. Похоже, что динамический SQL тоже не может спасти вас, потому что вам нужно set quoted_identifier а затем в той же динамической строке sql, и вы не можете, потому что одна динамика - это один пакет, и он должен быть первым оператором в пакете.

Но подождите, это еще не все.

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

Чтобы было понятно, что происходит, вот результат:

exec ('установить цитируемый_идентификатор; print @@ options & 256;')
exec ('установить значение quoted_identifier off; print @@options & 256;');
256
0

Так что ... ура! Нам удалось выполнить одну часть динамического sql (значение @both), и мы изменили настройку quoted_identifierвнутри этого! Прохладный. Чего это нам стоило? Вложенные динамические вызовы к exec().

Это нас спасает? Неа.

      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

Вложенные вызовы exec не помогайте нам, потому что (из документации):

Для вложенного пакета, использующего sp_executesql или exec(), синтаксический анализ начинается с использованием параметра сеанса QUOTED_IDENTIFIER. Если вложенный пакет находится внутри хранимой процедуры, синтаксический анализ начинается с использованием параметра QUOTED_IDENTIFIER хранимой процедуры. По мере анализа вложенного пакета любое появление SET QUOTED_IDENTIFIER изменит поведение синтаксического анализа с этого момента, но параметр сеанса QUOTED_IDENTIFIER не будет обновлен.

Что вы можете сделать вместо этого?

Запустите «курсор» за пределы пакета создания процедуры.

Например, напишите небольшую программу (или сценарий PowerShell), которая считывает определения резервного копирования вместе с необходимыми настройками, а затем выполняет каждую из них. create procedure как его собственная команда.

Или прочтите содержимое резервных копий и выгрузите все это в файл, включая инструкции «go» для каждой строки в таблице резервных копий. Выполните содержимое файла как сценарий.

Answer 2

Определите настройку вне динамического оператора. Если вы измените параметр внешней области, например, область динамического оператора унаследует и этот параметр.

Например:

      SET ANSI_NULLS OFF;
SELECT @@OPTIONS & 32; --Returns 0
EXEC sys.sp_executesql N'SELECT @@OPTIONS & 32;'; --Returns 0
GO

SET ANSI_NULLS ON;
SELECT @@OPTIONS & 32; --Returns 32
EXEC sys.sp_executesql N'SELECT @@OPTIONS & 32;'; --Returns 32

Поскольку последние 2 утверждения возвращаются 32 это означает, что ANSI_NULLSвключен в обоих операторах. Так же, 0 означает, что он отключен в предыдущих утверждениях.

Обратите внимание, что если вы измените параметр во внутренней (динамической) области, это изменение параметра не распространяется на внешнюю область:

      SELECT @@OPTIONS & 32; --returns 32
EXEC sys.sp_executesql N'SET ANSI_NULLS OFF; SELECT @@OPTIONS & 32;'; --returns 0
SELECT @@OPTIONS & 32; --Returns 32

Что касается причин возникновения ошибок, это потому, что это не оператор T-SQL, и поэтому он не распознается. GO является служебным заявлением и распознается IDE и интерфейсами командной строки (такими как SSMS, ADS и sqlcmd) в качестве разделителя партий. Он не распознается компилятором T-SQL, поэтому его не следует включать в динамические операторы.