Как условно создать хранимую процедуру в 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 в свой сценарий и заставить ее выполняться только в случае необходимости?
Ответы 11
Вот что я придумал:
Оберните его в EXEC (), например:
if @version <> @expects
begin
...snip...
end
else
begin
exec('CREATE PROC MyProc AS SELECT ''Victory!''');
end
Работает как шарм!
+1 за возможность обрабатывать условия, отличные от существования (в моем случае @@ Version определяет, созданы ли определенные процессы или нет)
+1 - Я использовал это, чтобы настроить свой генератор CRUD для создания процедуры-заглушки, если она еще не существует, и тогда изменил ее. Это позволяет мне вносить изменения в мои сохраненные процедуры, создавать CRUD, но сохранить любые разрешения, назначенные для существующих процессов.
Но следите за одинарными кавычками в вашей хранимой процедуре - их нужно «экранировать», добавив вторую. Первый ответ сделал это, но на случай, если вы его пропустили. Ловушка для молодых игроков.
этот ответ должен быть комментарием. Почему бы тебе не переместить его туда?
Версионирование вашей базы данных - это лучший способ, но ... Зачем создавать хранимые процедуры на определенных условиях. Для представлений, хранимых процедур, функций просто отбрасывайте их условно и каждый раз заново создавайте. Если вы создаете условно, вы не будете очищать базы данных, в которых есть проблема или взлом, который был добавлен 2 года назад другим разработчиком (вы или я никогда бы этого не сделали), который был уверен, что не забудет удалить один раз экстренное обновление.
потому что при воссоздании теряются метаданные, такие как дата создания
Должен признаться, я обычно согласен с @Peter - я условно отбрасываю, а затем безоговорочно воссоздаю каждый раз. В прошлом я слишком много раз попадался в ловушку, когда пытался угадать различия схем между базами данных, с контролем версий или без него.
Сказав это, ваше собственное предложение @Josh довольно круто. Конечно интересно. :-)
Проблема с удалением и созданием заключается в том, что вы теряете все права безопасности, которые ранее были применены к удаляемому объекту.
Проблема только в том, что вы не добавляете эти гранты повторно после воссоздания процедуры ;-).
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
используйте команду «Exists» в T-SQL, чтобы узнать, существует ли сохраненная процедура. Если это так, используйте «Изменить», иначе используйте «Создать».
Это старый поток, но Jobo неверен: Create Procedure должен быть первым оператором в пакете. Следовательно, вы не можете использовать Exists для проверки существования, а затем использовать Create или Alter. Жалость.
Верно :) (Кстати, это, вероятно, должен быть комментарий, а не ответ. Но я понимаю, что вы пока не можете "комментировать";)
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 | ВЫКЛЮЧЕННЫЙ }.
Мне нравится это решение, так как процедура, которую я пытался создать, уже содержала в себе динамический sql. Динамическое создание динамического sql - не самое увлекательное занятие.
Мое решение - проверить, существует ли процедура, если да, то отбросьте ее, а затем создайте процедуру (тот же ответ, что и @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
Итак, если процедура существовала, мы ее удаляем. Но что, если это была более новая, лучшая версия процедуры? Нехорошо просто слепо отбрасывать и перезаписывать. Во многих случаях, если он уже существует, и мы не ожидали, что он будет существовать, просто оставьте его в покое и проведите небольшое расследование, чтобы увидеть, откуда он появился, и действительно ли вы можете его перезаписать.
У меня вопрос, почему люди редактируют сценарии на сервере, не фиксируя эти изменения в системе управления версиями. Откуда они знают, что их изменения не будут слепо перезаписаны каким-то другим актером? Если это решение не работает для вас, не используйте его, но приходить сюда, чтобы пожаловаться, что оно не соответствует вашему конкретному варианту использования, никому не помогает, и, честно говоря, если у вас есть люди, непосредственно редактирующие процессы в вашем SQL Сервер без фиксации этих изменений в системе управления версиями, тогда у вас будут большие проблемы IMO.
Я не жалуюсь, просто предупреждаю, потому что я бывал в неприятных ситуациях, когда некоторые большие старые проекты не справлялись с управлением и два или более отдельных разработчика из разных компаний поддерживают копии одной и той же базы данных в разных средах. Таким образом, sproc может существовать на одном сервере, но не на другом, поэтому необходимо применить сценарий, который вообще ничего не делает (не сбой, не изменяет), в случае, если sproc уже существует, и создает новый. если нет. Но да, вы правы, в этом не должно быть необходимости, если у вас хороший менеджмент и единственный источник власти.
Гораздо лучше изменить существующую хранимую процедуру из-за возможности для свойств и разрешений, которые были добавлены И которые будут потеряны, если сохраненная процедура будет отброшена.
Итак, проверьте, НЕ СУЩЕСТВУЕТ ли он, если он не создает фиктивный процесс. Затем после этого используйте оператор 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]
....
Тем не менее, это меняет процедуру, если она существует. Что, если кто-то уже создал новую версию? Как избежать изменения процедуры, если она уже существует, и создания новой только в том случае, если она не существует, чтобы не потерять существующий код процедуры?
@JustAMartin, это не контроль версий, а просто инструкция Alter или Create. Мне не известны какие-либо собственные или встроенные средства управления версиями для SQL Server или любой другой базы данных. Думаю, нужно что-то создавать. Возможно, пользовательское свойство, что-то в комментариях к самой хранимой процедуре? Я хочу сказать, что при удалении процесса, если он уже существует, потенциально теряются важные атрибуты, которые не восстанавливаются при создании.
@JustAMartin Я вижу, что неправильно прочитал ваш комментарий. Если кто-то хочет создать хранимую процедуру только тогда, когда такой процедуры с таким же именем не существует, просто используйте create. Это не удастся, если процедура уже определена.
@JustAMartin Я вижу, что неправильно прочитал ваш комментарий. Если кто-то хочет создать хранимую процедуру только тогда, когда такой процедуры с таким же именем не существует, просто используйте create. Это не удастся, если процедура уже определена.
Да, это проблема MS SQL Server; нет четкого способа сказать это - если sproc существует, просто пропустите его (и также не выдавайте ошибки - просто пропустите попытки его обновления).
Повторное создание процедур каждый раз (условно отбрасывание) - гораздо лучшее решение. Если использовать exec, вы ничего не получите, и есть недостатки; ваша процедура должна экранировать строки, и любые номера строк в ошибках будут относиться к номеру строки относительно команды exec.