Создание хранимой процедуры, если она еще не существует

Ошибка CREATE PROCEDURE must be the first statement in the batch возникает из-за того, что в SQL Server определение процедуры должно быть записано в отдельном пакете без предварительных инструкций. Обычно я использую следующий подход:

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'myProc'
            AND type = 'P'
      )
     DROP PROCEDURE dbo.myProc
GO

CREATE PROCEDURE dbo.myProc
AS
BEGIN
    -- логика процедуры здесь
END
GO

GRANT EXECUTE ON dbo.myProc TO MyUser 

Не забудьте добавить инструкции GRANT для предоставления прав на выполнение процедуры, так как они будут потеряны, если вы пересоздадите процедуру.

Также стоит учесть, что в процессе развертывания хранимых процедур может возникнуть ситуация, когда команда DROP выполнится успешно, а команда CREATE провалится. Я всегда пишу свои SQL-скрипты с использованием транзакций для обеспечения возможности отката в случае ошибки. Обязательно не удаляйте код COMMIT/ROLLBACK в конце, иначе ваш DBA может быть недоволен 😃

BEGIN TRAN 
IF EXISTS (
       SELECT type_desc, type
       FROM sys.procedures WITH(NOLOCK)
       WHERE NAME = 'myProc'
           AND type = 'P'
     )
DROP PROCEDURE myProc 
GO
CREATE PROCEDURE myProc
AS
BEGIN
    -- логика процедуры здесь
END
GO

-- НАЧАЛО: НЕ УДАЛЯЙТЕ ЭТОТ КОД (он выполняет коммит или откат удаления процедуры)
IF EXISTS(
       SELECT 1
       FROM sys.procedures WITH(NOLOCK)
       WHERE NAME = 'myProc'
           AND type = 'P'
     )
    COMMIT TRAN
ELSE
    ROLLBACK TRAN
-- КОНЕЦ: НЕ УДАЛЯЙТЕ ЭТОТ КОД

Таким образом, вы обеспечиваете большую надежность при развертывании хранимых процедур.

Один из идиоматических способов, который я использую в последнее время и который мне очень нравится, выглядит следующим образом:

if exists (select 1 from sys.objects where object_id = object_id('dbo.yourProc'))
   set noexec on
go
create procedure dbo.yourProc as
begin
   select 1 as [не реализовано]
end
go
set noexec off
alter procedure dbo.yourProc as
begin
   /*тело процедуры здесь*/
end

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

[Правка 2018-02-09] - В SQL 2016 SP1 для команд create procedure и drop procedure были добавлены синтаксические улучшения, которые помогают в таких ситуациях. В частности, вы теперь можете сделать так:

create or alter dbo.yourProc as
go

drop procedure if exists dbo.yourProc;

Обе конструкции обеспечивают идемпотентность в предполагаемом запросе (т.е. вы можете выполнять их несколько раз, и желаемое состояние будет достигнуто). Это то, как я бы сделал это сейчас (при условии, что вы используете версию SQL Server, которая это поддерживает).

Вижу, что существует принятый ответ, но он не вполне соответствует тому, что спрашивает автор вопроса, а именно — как создать процедуру, если она не существует. Приведенный ниже код всегда работает и имеет преимущество в том, что не требует удаления процедур, что может вызвать проблемы при использовании аутентификации SQL.

USE [MyDataBase]
GO

IF OBJECT_ID('mySchema.myProc') IS NULL
    EXEC('CREATE PROCEDURE mySchema.myProc AS SET NOCOUNT ON;')
GO

ALTER PROCEDURE mySchema.myProc
    @DeclaredParmsGoHere    DataType

AS 
BEGIN
    DECLARE @AnyVariablesINeed    Their DataType
    SELECT myColumn FROM myTable WHERE myIndex = @IndexParm
END

Таким образом, этот код сначала проверяет существует ли процедура mySchema.myProc. Если она не существует, создается временная версия процедуры. Затем вы можете обновить ее с помощью ALTER PROCEDURE, добавив необходимые параметры и логику.

Ваша процедура spGetRailItems предназначена для выборки данных в зависимости от конфигурации, представленной в таблице setups. В ней проверяется наличие настройки isLiftedBagsEnable, и в зависимости от её значения выполняется выборка данных из таблицы ProcData.

Вот детальное объяснение кода:

1. Создание процедуры: Процедура создаётся только в том случае, если она ещё не существует в базе данных. Это обеспечивается конструкцией IF NOT EXISTS.

2. Переменная @isLiftedBagsEnable: Эта переменная инициализируется по умолчанию значением 1 (разрешено). Дальше её значение может быть обновлено в зависимости от записей в таблице setups.

3. Проверка настройки: Установка @isLiftedBagsEnable выполняется на основе значения из таблицы setups, которое соотносится с настройками системы.

4. Основная логика выбора:

   • Если @isLiftedBagsEnable равно 1, проверяется, существуют ли элементы в таблице ITEMCONFIG.
     • Если да, то выбираются элементы только из ProcData, которые есть в ItemConfig.
     • Если нет, выбираются все элементы из ProcData.
   • Если @isLiftedBagsEnable не равно 1, все элементы из ProcData выбираются без дополнительных фильтров.

5. Возврат результатов: В конечном итоге, данные возвращаются в отсортированном по описанию виде.

Вы можете вызвать эту процедуру с помощью команды exec spGetRailItems;.

Если у вас возникнут дополнительные вопросы о работе этой процедуры или о том, как её настроить, не стесняйтесь спрашивать!

Если вы используете SQL Server 2016, то есть более короткий способ проверить существование хранимой процедуры, а затем удалить и заново создать её. Вот пример кода, который можно использовать:

USE [DATABASENAME]
GO
DROP PROCEDURE IF EXISTS <имя процедуры>
GO
CREATE PROCEDURE <имя процедуры>
AS
-- ваш скрипт здесь
END
GO
GRANT EXECUTE ON <имя процедуры> TO <имя пользователя>

Источник: Microsoft MSDN