Erstellen von gespeicherten Prozeduren (Datenbankmodul)

Sie können gespeicherte Prozeduren mithilfe der CREATE PROCEDURE-Anweisung von Transact-SQL erstellen.

Bevor Sie eine gespeicherte Prozedur erstellen, sollten Sie Folgendes berücksichtigen:

• CREATE PROCEDURE-Anweisungen können nicht mit anderen SQL-Anweisungen in einem einzigen Batch kombiniert werden.
• Damit Sie Prozeduren erstellen können, müssen Sie die CREATE PROCEDURE-Berechtigung in der Datenbank und die ALTER-Berechtigung für das Schema besitzen, in dem die Prozedur erstellt wird. Für CLR-gespeicherte Prozeduren müssen Sie entweder der Besitzer der Assembly sein, auf die in <method_specifier> verwiesen wird, oder Sie müssen die REFERENCES-Berechtigung für die betreffende Assembly besitzen.
• Gespeicherte Prozeduren sind Objekte im Schemabereich, und ihre Namen müssen den Regeln für Bezeichner entsprechen.
• Eine gespeicherte Prozedur kann nur in der aktuellen Datenbank erstellt werden.

Wenn Sie eine gespeicherte Prozedur erstellen, sollten Sie Folgendes angeben:

• Alle Eingabe- und Ausgabeparameter für die aufrufende Prozedur oder den aufrufenden Batch.
• Die Programmierungsanweisungen, die Operationen in der Datenbank ausführen, einschließlich der Aufrufe anderer Prozeduren.
• Den Statuswert, der an eine aufrufende Prozedur oder einen aufrufenden Batch zurückgegeben wird und der Erfolg oder Fehlschlagen (sowie die Ursache) anzeigt.
• Alle Fehlerbehandlungsanweisungen müssen mögliche Fehler abfangen und behandeln können.
  Microsoft SQL Server 2005 führt neue Fehlerbehandlungsfunktionen wie z. B. ERROR_LINE und ERROR_PROCEDURE ein, die in der gespeicherten Prozedur angegeben werden können. Weitere Informationen finden Sie unter Verwenden von TRY...CATCH in Transact-SQL.

Benennen gespeicherter Prozeduren

Es empfiehlt sich, keine gespeicherten Prozeduren mit sp_ als Präfix zu erstellen. SQL Server verwendet das Präfix sp_, um gespeicherte Systemprozeduren zu definieren. Der von Ihnen ausgewählte Name kann andernfalls ggf. einen Konflikt mit zukünftigen Systemprozeduren verursachen. Wenn Ihre Anwendung nicht schemaqualifizierte Namensverweise verwendet und Ihr eigener Prozedurname einen Konflikt mit einem Systemprozedurnamen verursacht, dann bricht Ihre Anwendung ab, weil der Name die Verbindung zur Systemprozedur und nicht zu Ihrer eigenen Prozedur herstellt.

Eine benutzerdefinierte gespeicherte Prozedur, die den gleichen Namen wie eine gespeicherte Systemprozedur besitzt und nicht qualifiziert oder im dbo-Schema definiert ist, wird niemals ausgeführt; stattdessen wird immer die gespeicherte Systemprozedur ausgeführt. Im folgenden Beispiel wird dieses Verhalten veranschaulicht.

USE AdventureWorks;
GO
CREATE PROCEDURE dbo.sp_who
AS
    SELECT FirstName, LastName FROM Person.Contact;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO

Das Verwenden eines expliziten Schemaqualifizierers bietet außerdem geringfügige Leistungsvorteile. Die Namensauflösung erfolgt etwas schneller, wenn das Datenbankmodul nicht mehrere Schemas durchsuchen muss, um die Prozedur zu finden. Weitere Informationen finden Sie unter Ausführen von gespeicherten Prozeduren (Datenbankmodul).

Temporär gespeicherte Prozeduren

Private und globale temporär gespeicherte Prozeduren können, analog zu temporären Tabellen, mit dem Präfix # oder ## erstellt werden, das dem Prozedurnamen hinzugefügt wird. # kennzeichnet eine lokale temporär gespeicherte Prozedur; ## kennzeichnet eine globale temporär gespeicherte Prozedur. Diese Prozeduren sind nach dem Herunterfahren von SQL Server nicht mehr vorhanden.

Temporär gespeicherte Prozeduren sind hilfreich, wenn Sie eine Verbindung mit einer früheren Version von SQL Server herstellen, die nicht die erneute Verwendung von Ausführungsplänen für Transact-SQL-Anweisungen oder -Batches unterstützt. Anwendungen, die eine Verbindung mit SQL Server 2000 oder höher herstellen, sollten anstelle von temporär gespeicherten Prozeduren die gespeicherte Systemprozedur sp_executesql verwenden. Nur die Verbindung, die eine lokale temporäre Prozedur erstellt hat, kann diese Prozedur auch ausführen. Die Prozedur wird automatisch gelöscht, wenn die Verbindung geschlossen wird.

Eine globale temporär gespeicherte Prozedur kann von jeder Verbindung ausgeführt werden. Eine globale temporär gespeicherte Prozedur bleibt so lange bestehen, bis die Verbindung des Benutzers geschlossen wird, der die Prozedur erstellt hat, und alle aktuell für andere Verbindungen ausgeführten Versionen der Prozedur beendet sind. Nachdem die Verbindung geschlossen wurde, die zum Erstellen der Prozedur verwendet wurde, ist keine weitere Ausführung der globalen temporär gespeicherten Prozedur zulässig. Nur die Verbindungen, die bereits mit der Ausführung der gespeicherten Prozedur begonnen haben, können die Ausführung beenden.

Wenn eine gespeicherte Prozedur, die nicht das Präfix # oder ## trägt, direkt in der tempdb-Datenbank erstellt wird, wird die gespeicherte Prozedur automatisch gelöscht, wenn SQL Server heruntergefahren wird, da tempdb bei jedem Start von SQL Server neu erstellt wird. Prozeduren, die direkt in tempdb erstellt werden, sind auch dann noch vorhanden, wenn die erstellende Verbindung bereits beendet wurde.

Hinweis:
Die häufige Verwendung temporärer gespeicherter Prozeduren kann zu Konflikten für die Systemtabellen in tempdb führen und sich negativ auf die Leistung auswirken. Stattdessen sollte sp_executesql verwendet werden. sp_executesql speichert keine Daten in den Systemtabellen und vermeidet somit das Problem.

CLR-gespeicherte Prozeduren können nicht als temporär gespeicherte Prozeduren erstellt werden.

Beispiele

A. Verwenden einer einfachen Prozedur mit einer komplexen SELECT-Anweisung

Diese gespeicherte Prozedur gibt alle Mitarbeiter (mit Vor- und Nachnamen), ihre Titel und ihre Abteilungsnamen aus einer Sicht zurück. Diese gespeicherte Prozedur verwendet keine Parameter.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

Die gespeicherte Prozedur uspGetEmployees kann auf folgende Arten ausgeführt werden:

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

B. Verwenden einer einfachen Prozedur mit Parametern

Diese gespeicherte Prozedur gibt nur die angegebene Mitarbeiterin (mit Vor- und Nachnamen), ihren Titel und ihren Abteilungsnamen aus einer Sicht zurück. Diese gespeicherte Prozedur nimmt genaue Übereinstimmungen für die übergebenen Parameter an.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 
    
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

Die gespeicherte Prozedur uspGetEmployees kann auf folgende Arten ausgeführt werden:

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

C. Verwenden einer einfachen Prozedur mit Platzhalterparametern

Diese gespeicherte Prozedur gibt nur die angegebenen Mitarbeiter (mit Vor- und Nachnamen), ihre Titel und ihre Abteilungen aus einer Sicht zurück. Diese gespeicherte Prozedur führt mit den übergebenen Parametern einen Mustervergleich aus oder verwendet die vordefinierten Standardwerte (Nachnamen, die mit dem Buchstaben D beginnen), wenn keine Parameter angegeben werden.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2 
    @LastName nvarchar(50) = N'D%', 
    @FirstName nvarchar(50) = N'%'
AS 
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

Die gespeicherte Prozedur uspGetEmployees2 kann in verschiedenen Kombinationen ausgeführt werden. Hier werden nur einige Kombinationen gezeigt.

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

D. Verwenden von OUTPUT-Parametern

Das folgende Beispiel erstellt die gespeicherte Prozedur uspGetList, die eine Liste der Produkte zurückgibt, die Preise aufweisen, die einen angegebenen Betrag nicht übersteigen. Dieses Beispiel zeigt die Verwendung mehrerer SELECT-Anweisungen und mehrerer OUTPUT-Parameter. OUTPUT-Parameter ermöglichen einer externen Prozedur, einem Batch oder mehreren Transact-SQL-Anweisungen den Zugriff auf einen Satz von Werten während der Ausführung der Prozedur.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40) 
    , @MaxPrice money 
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
        FROM Production.Product AS p
        JOIN  Production.ProductSubcategory AS s 
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

Mit uspGetList wird eine Liste mit Adventure Works-Produkten (Fahrrädern) zurückgegeben, die weniger als 700 $ kosten. Die OUTPUT-Parameter @Cost und @Compareprices werden mit der Control-of-flow-Sprache verwendet, um eine Meldung im Fenster Meldungen zurückzugeben.

Hinweis:
Die OUTPUT-Variable muss sowohl beim Erstellen der Prozedur als auch beim Verwenden der Variablen definiert sein. Der Parametername und der Variablenname müssen nicht übereinstimmen; jedoch müssen der Datentyp und die Position des Parameters übereinstimmen (es sei denn, es wird @Listprice= variable verwendet).

DECLARE @ComparePrice money, @Cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @ComparePrice OUT, 
    @Cost OUTPUT
IF @Cost <= @ComparePrice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

Im Folgenden wird ein Teil des Resultsets aufgeführt:

Product                                            List Price
-------------------------------------------------- ------------------
Road-750 Black, 58                                 539.99
Mountain-500 Silver, 40                            564.99
Mountain-500 Silver, 42                            564.99
...
Road-750 Black, 48                                 539.99
Road-750 Black, 52                                 539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

Siehe auch

Aufgaben

Vorgehensweise: Erstellen einer gespeicherten Prozedur (SQL Server Management Studio)

Konzepte

Rollbacks und Commits in gespeicherten Prozeduren und Triggern
Erstellen CLR-gespeicherter Prozeduren
Ausführen von gespeicherten Prozeduren (Datenbankmodul)
Ändern gespeicherter Prozeduren
Erneutes Kompilieren gespeicherter Prozeduren
Anzeigen gespeicherter Prozeduren
Löschen gespeicherter Prozeduren

Andere Ressourcen

CREATE PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL)
EXECUTE AS-Klausel (Transact-SQL)
REVERT (Transact-SQL)
Kontextwechsel
Angeben von Parametern
Zurückgeben von Daten von einer gespeicherten Prozedur

Hilfe und Informationen

Informationsquellen für SQL Server 2005