Как условно создать хранимую процедуру в SQL Server?

В рамках моей стратегии интеграции у меня есть несколько сценариев SQL, которые запускаются для обновления базы данных. Первое, что делают все эти скрипты, - проверить, нужно ли им запускать, например:

if @version <> @expects
    begin
        declare @error varchar(100);
        set @error = 'Invalid version. Your version is ' + convert(varchar, @version) + '. This script expects version ' + convert(varchar, @expects) + '.';
        raiserror(@error, 10, 1);
    end
else
    begin
        ...sql statements here...
    end

Отлично работает! За исключением случаев, когда мне нужно добавить хранимую процедуру. Команда "create proc" должна быть единственной командой в группе команд sql. Помещение "create proc" в мой оператор IF вызывает эту ошибку:

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

Ой! Как поместить команду CREATE PROC в мой script и выполнить ее, только если это необходимо?

Ответ 1

Вот что я придумал:

Оберните его в EXEC(), например:

if @version <> @expects
    begin
        ...snip...
    end
else
    begin
        exec('CREATE PROC MyProc AS SELECT ''Victory!''');
    end

Работает как шарм!

Ответ 2

SET NOEXEC ON - отличный способ отключить часть кода

IF NOT EXISTS (SELECT * FROM sys.assemblies WHERE name = 'SQL_CLR_Functions')
  SET NOEXEC ON
GO
CREATE FUNCTION dbo.CLR_CharList_Split(@list nvarchar(MAX), @delim nchar(1) = N',')
RETURNS TABLE (str nvarchar(4000)) AS EXTERNAL NAME SQL_CLR_Functions.[Granite.SQL.CLR.Functions].CLR_CharList_Split
GO
SET NOEXEC OFF

Найдено здесь: https://codereview.stackexchange.com/questions/10490/conditional-create-must-be-the-only-statement-in-the-batch

P.S. Другой способ - SET PARSEONLY {ON | ВЫКЛ}.

Ответ 3

Но следите за одиночными кавычками в своей хранимой процедуре - их нужно "экранировать", добавив второй. Первый ответ сделал это, но на всякий случай вы его пропустили. Ловушка для молодых игроков.

Ответ 4

Вершина вашей базы данных - путь, но... Зачем условно создавать хранимые процедуры. Для представлений, хранимых процедур, функций, просто условно отбрасывать их и повторно создавать их каждый раз. Если вы условно создадите, то вы не будете очищать базы данных, у которых есть проблема или взлома, которые были добавлены еще 2 года назад другим разработчиком (вы или я этого никогда не сделаем), который был уверен, что он не забудет удалить одно время аварийное обновление.

Ответ 5

Проблема с отбрасыванием и созданием заключается в том, что вы теряете любые гранты безопасности, которые ранее были применены к отбрасываемому объекту.

Ответ 6

Должен признаться, я обычно соглашаюсь с @Peter - я условно падаю, а затем безоговорочно воссоздаю каждый раз. Я был пойман слишком много раз в прошлом, когда пытался угадать различия между схемами между базами данных с помощью или без контроля версий.

Сказав это, ваше собственное предложение @Josh довольно круто. Конечно, интересно.: -)

Ответ 7

Это старый поток, но Jobo неверен: Create Procedure должен быть первым оператором в пакете. Поэтому вы не можете использовать Exists для проверки на существование, а затем использовать либо Create, либо Alter. Жалость.

Ответ 8

Мое решение состоит в том, чтобы проверить, существует ли proc, если да, то отбросьте его, а затем создайте proc (такой же ответ, как у @robsoft, но с примером...)

IF EXISTS(SELECT * FROM sysobjects WHERE Name = 'PROC_NAME' AND xtype='P') 
BEGIN
    DROP PROCEDURE PROC_NAME
END
GO
CREATE PROCEDURE PROC_NAME
    @value  int
AS
BEGIN
    UPDATE  SomeTable
    SET     SomeColumn = 1
    WHERE   Value = @value
END
GO

Ответ 9

IF NOT EXISTS(SELECT * FROM sys.procedures WHERE name = 'pr_MyStoredProc')
BEGIN

     CREATE PROCEDURE pr_MyStoredProc AS .....
     SET NOCOUNT ON
END

ALTER PROC pr_MyStoredProc
AS
SELECT * FROM tb_MyTable

Ответ 10

Гораздо лучше изменить существующий хранимый процесс из-за возможного добавления свойств и разрешений, которые будут потеряны, если сохраненный процесс будет удален.

Итак, проверьте, не существует ли он, если он не существует, то создайте фиктивный процесс. Затем после этого используйте оператор alter.

IF NOT EXISTS(SELECT * FROM sysobjects WHERE Name = 'YOUR_STORED_PROC_NAME' AND xtype='P')
EXECUTE('CREATE PROC [dbo].[YOUR_STORED_PROC_NAME] as BEGIN select 0 END')
GO
ALTER  PROC [dbo].[YOUR_STORED_PROC_NAME]
....

Ответ 11

используйте команду "Exists" в T-SQL, чтобы узнать, существует ли сохраненный процесс proc. Если это так, используйте "Alter", иначе используйте "Create"