Freigeben über

Gewusst wie: Erstellen von gespeicherten Prozeduren und benutzerdefinierten Funktionen

  • Artikel

Mithilfe von Server-Explorer können gespeicherte Prozeduren erstellt werden. Gespeicherte Prozeduren können komplexe Geschäftsregeln definieren, die Änderung von Daten steuern, den Zugriff mithilfe von Sicherheitsberechtigungen einschränken, Transaktionsintegrität bereitstellen und allgemein die von der Anwendung benötigten Datenbankarbeiten ausführen.

Unter Microsoft SQL Server 2000 und späteren Versionen können Sie mit Server-Explorer auch benutzerdefinierte Funktionen erstellen. Eine benutzerdefinierte Funktion stellt eine Routine dar, die nützliche Logik für die Verwendung in anderen Abfragen einschließt. Während Ansichten auf eine einzelne SELECT-Anweisung beschränkt sind, können benutzerdefinierte Funktionen mehrere SELECT-Anweisungen aufweisen und eine leistungsfähigere Logik bereitstellen, als dies mit Ansichten möglich ist.

Benutzerdefinierte Funktionen geben immer einen Wert zurück. Je nach Typ des zurückgegebenen Werts fällt jede benutzerdefinierte Funktion unter eine von drei Kategorien:

  • **Skalarwertfunktion   **Eine benutzerdefinierte Funktion kann einen skalaren Wert zurückgeben, wie eine ganze Zahl oder einen Timestamp. Wenn eine Funktion einen skalaren Wert zurückgibt, können Sie sie in einer Abfrage überall dort einsetzen, wo Sie einen Spaltennamen verwenden würden.

  • Inlinefunktion   Wenn eine benutzerdefinierte Funktion eine einzelne SELECT-Anweisung enthält und diese Anweisung aktualisierbar ist, ist auch das von der Funktion zurückgegebene tabellarische Ergebnis aktualisierbar. Solche Funktionen werden als Inlinefunktionen bezeichnet. Wenn eine Inlinefunktion eine Tabelle zurückgibt, können Sie diese Funktion in der FROM-Klausel einer anderen Abfrage verwenden. Weitere Informationen finden Sie unter Gewusst wie: Erstellen von Abfragen mit anderen Quellen als einer Tabelle.

  • Tabellenwert-Funktion   Wenn eine benutzerdefinierte Funktion mehrere SELECT-Anweisungen oder eine nicht aktualisierbare SELECT-Anweisung enthält, ist das von dieser Funktion zurückgegebene tabellarische Ergebnis nicht aktualisierbar. Wenn eine Tabellenwertfunktion eine Tabelle zurückgibt, können Sie diese Funktion in der FROM-Klausel einer anderen Abfrage verwenden.

Tipp

In den folgenden Beispielen ist "DBO" ein Akronym für den Datenbankbesitzer (Database Owner) und wird verwendet, um den Namen der gespeicherten Prozedur und der benutzerdefinierten Funktion zu qualifizieren. Der DBO ist ein Benutzer, der über implizite Berechtigungen zum Durchführen aller Aktivitäten in der Datenbank verfügt. Jedes von einem Mitglied der Sysadmin-Rolle erstellte Objekt gehört automatisch zum DBO. In den folgenden Beispielen ist der DBO-Namensqualifizierer enthalten.

Tipp

Je nach den aktiven Einstellungen oder der Version unterscheiden sich die Dialogfelder und Menübefehle auf Ihrem Bildschirm möglicherweise von den in der Hilfe beschriebenen. Klicken Sie im Menü Extras auf Einstellungen importieren und exportieren, um die Einstellungen zu ändern. Weitere Informationen finden Sie unter Arbeiten mit Einstellungen.

So erstellen Sie eine neue gespeicherte Prozedur

  1. Klicken Sie im Server-Explorer mit der rechten Maustaste auf den Ordner Gespeicherte Prozeduren oder auf eine beliebige gespeicherte Prozedur in diesem Ordner.

  2. Klicken Sie im Kontextmenü auf den Befehl Neue gespeicherte Prozedur hinzufügen.

    Es wird eine neue gespeicherte Prozedur mit den folgenden SQL-Anweisungen als Skelett erstellt:

    CREATE PROCEDURE dbo.StoredProcedure1
/*
   (
      @parameter1 datatype = default value,
      @parameter2 datatype OUTPUT
   )
*/
AS
   /* SET NOCOUNT ON */
   RETURN

  3. StoredProcedure1 in der ersten Zeile kann durch den Namen der neuen Prozedur ersetzt werden. Beispielsweise können Sie MyProcedure als Namen verwenden:

    CREATE PROCEDURE dbo.MyProcedure

    Tipp

    Gespeicherte Prozeduren müssen eindeutige Namen aufweisen. Wenn Sie einen Namen wählen, der bereits einer anderen gespeicherten Prozedur zugewiesen ist, werden Sie durch eine Fehlermeldung gewarnt, dass bereits eine gespeicherte Prozedur gleichen Namens vorhanden ist.

  4. Schreiben Sie den restlichen Prozedurtext in SQL.

    Weitere Informationen und Beispiele für gespeicherte Prozeduren finden Sie in der Dokumentation des Datenbankservers. Wenn Sie Microsoft® SQL Server™ verwenden, finden Sie weitere Informationen in SQL Server Books Online unter "CREATE PROCEDURE".

So erstellen Sie eine neue benutzerdefinierte Funktion

  1. Klicken Sie im Server-Explorer mit der rechten Maustaste auf den Ordner Funktionen oder auf eine beliebige andere Funktion in diesem Ordner.

  2. Zeigen Sie im Kontextmenü auf Neu hinzufügen, und wählen Sie dann Inlinefunktion, Tabellenwert-Funktion oder Skalarwertfunktion.

    Tipp

    Das Skelett aus SQL-Anweisungen für eine neue Funktion kann nicht in die SQL-Anweisungen für einen anderen Funktionstyp geändert und anschließend gespeichert werden. Wenn Sie z. B. mit einer Inlinefunktion beginnen, können Sie die SQL-Anweisungen nicht zu einer Skalarwertfunktion ändern. Der Speichervorgang schlägt fehl.

    Es wird eine neue benutzerdefinierte Funktion mithilfe eines Skeletts aus SQL-Anweisungen erstellt. Wenn Sie z. B. Skalarwertfunktion wählen, wird das folgende Skelett aus SQL-Anweisungen angezeigt:

    CREATE FUNCTION dbo.Function1
   (
   /*
   @parameter1 datatype = default value,
   @parameter2 datatype
   */
   )
RETURNS /* datatype */
AS
   BEGIN
      /* sql statement ... */
   RETURN /* value */
   END

  3. Function1 in der ersten Zeile kann durch den Namen der neuen Funktion ersetzt werden. Beispielsweise können Sie MyFunction als Namen verwenden:

    CREATE FUNCTION dbo.MyFunction

    Tipp

    Benutzerdefinierte Funktionen müssen eindeutige Namen aufweisen. Wenn Sie einen Namen wählen, der bereits einer anderen Funktion zugewiesen ist, werden Sie durch eine Fehlermeldung gewarnt, dass bereits eine Funktion gleichen Namens vorhanden ist.

  4. Schreiben Sie den restlichen Funktionstext in SQL.

Tipp

Wenn die gewünschte Option nicht im Kontextmenü angezeigt wird, wird dieses Feature in der von Ihnen verwendeten Version von Visual Studio möglicherweise nicht unterstützt. Weitere Informationen finden Sie unter Versionen von Visual Database Tools.

Weitere Informationen und Beispiele für benutzerdefinierte Funktionen finden Sie in der Dokumentation des Datenbankservers. Wenn Sie Microsoft® SQL Server™ verwenden, finden Sie weitere Informationen in SQL Server Books Online unter "CREATE FUNCTION".

Siehe auch

Konzepte

Ausdrücke in Abfragen

Weitere Ressourcen

Arbeiten mit gespeicherten Prozeduren und benutzerdefinierten Funktionen