Utwórz nową funkcję za pomocą kodu, jeśli nie istnieje

Chcę utworzyć nową funkcję za pomocą skryptu w mojej bazie danych. Kod skryptu znajduje się poniżej:

IF Exists(Select * From sys.sysobjects A Where A.name =N'fn_myfunc' and xtype=N'FN') return;

CREATE FUNCTION fn_myfunc ()
returns varchar(10)
AS Begin
...
End

Ale kiedy wykonuję powyższy skrypt, SQL Server zwraca błąd:

'CREATE FUNCTION' must be the first statement in a query batch.

Aktualizacja stycznia 2017 r. - SQL Server 2016+ / Azure SQL Database

SQL Server 2016 i bieżąca wersja bazy danych SQL Azure ma teraz następującą składnię dla funkcji, procedur, tabel, baz danych itp. ( DROP IF EXISTS):

DROP FUNCTION IF EXISTS dbo.fn_myfunc;

Dodatek Service Pack 1 dla programu SQL Server 2016 dodaje jeszcze lepszą funkcjonalność modułów (funkcje, procedury, wyzwalacze, widoki), co oznacza brak utraty uprawnień lub zależności ( CREATE OR ALTER):

CREATE OR ALTER FUNCTION dbo.fn_myfunc ...

Oba te ulepszenia składni mogą prowadzić do znacznie prostszych skryptów używanych do kontroli źródła, wdrażania itp.

Ale jeśli używasz ...

starsza wersja

Musisz zrobić to, co robi SQL Server, kiedy piszesz to z Management Studio:

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE type = 'FN' AND name = 'fn_myfunc')
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'CREATE FUNCTION ...';
    EXEC sp_executesql @sql;
END

Lub możesz powiedzieć:

BEGIN TRY
    DROP FUNCTION dbo.fn_myfunc;
END TRY
BEGIN CATCH
    PRINT 'Function did not exist.';
END CATCH
GO
CREATE FUNCTION...

Lub możesz po prostu powiedzieć:

DROP FUNCTION dbo.fn_myfunc;
GO
CREATE FUNCTION...

(Tutaj pojawi się komunikat o błędzie, jeśli funkcja jeszcze nie istnieje, ale skrypt będzie kontynuował od następnego GO, więc niezależnie od tego, czy upuszczenie działało, czy nie, funkcja będzie nadal (ponownie) tworzona).

Pamiętaj, że jeśli upuścisz tę funkcję i utworzysz ją ponownie, utracisz również uprawnienia i potencjalnie informacje o zależnościach.

Błąd jest dość oczywisty. Istnieje kilka sposobów, aby to naprawić.

1. Rozdziel skrypt na różne partie w Management Studio za pomocą GOpseudo-słowa kluczowego i DROP/ CREATEobiektu. (Pamiętaj, że samo słowo kluczowe można zmienić w opcjach Management Studio, ale jest to ustawienie de facto, więc sugeruję pozostawienie go w spokoju).

   Po uruchomieniu skryptu (lub wybranej części skryptu) Management Studio oddziela każdą część skryptu między GOs i kolejno wysyła części do SQL Server jako osobne partie.

2. Użyj dynamicznego SQL, aby wysłać osobną partię z innej partii.

   Jest to preferowana metoda, ponieważ wtedy twój skrypt nie zależy od zewnętrznej funkcjonalności, aby poprawnie się uruchomić. Na przykład, jeśli aplikacja ma program do aktualizacji bazy danych, ogólnie mówiąc, załaduje plik skryptu, a następnie uruchomi go na serwerze docelowym. Albo będziesz musiał dodać logikę, aby oddzielić partie, jak robi to Management Studio (uwaga: pełna niebezpieczeństw), lub napisać skrypt w taki sposób, aby cały skrypt mógł zostać pomyślnie wykonany jako pojedyncza partia.

   Jak wspomniano w innej odpowiedzi, możesz wykonać test / CREATEprzy użyciu tej metody (lub innej kombinacji DROP/ CREATEitp.). Wolę utworzyć obiekt pośredniczący, jeśli obiekt nie istnieje, a następnie użyć ALTER <object>go do utworzenia lub zmiany. To podejście nie usuwa zależności, takich jak uprawnienia lub właściwości rozszerzone, i nie jest konieczne kopiowanie / wklejanie podatnej na błędy logiki, aby wykonać CREATE/ ALTERw pojedynczej instrukcji.

   Oto szablon, którego używam do tworzenia lub zmiany funkcji skalarnej. Zostawię to jako ćwiczenie dla czytelnika, aby dostosować to do innych typów obiektów (przechowywane procy, wyzwalacze itp.).

IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[<schema>].[<function name>]') AND type IN ('FN', 'FS'))
    EXEC sp_executesql N'CREATE FUNCTION [<schema name>].[<function name>] (@a int) RETURNS int AS BEGIN /* Stub */ RETURN @a END'

EXEC sp_executesql N'
ALTER FUNCTION [<schema name>].[<function name>]
/* ... */
'

Możesz sprawdzić, czy obiekt istnieje, databasei utworzyć, jeśli nie:

IF OBJECT_ID('new_function', 'FN') IS NULL
BEGIN
  EXEC('CREATE FUNCTION new_function() RETURNS INT AS BEGIN RETURN 1 END');
END;
go

ALTER FUNCTION new_function() RETURNS INT AS
BEGIN

...