Erstellen von gespeicherten Prozeduren (Datenbankmodul)

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 AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.sp_who
AS
    SELECT FirstName, LastName FROM Person.Person;
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 Database Engine (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

USE AdventureWorks2008R2;
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

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

USE AdventureWorks2008R2;
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,Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

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

USE AdventureWorks2008R2;
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

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. In dem Beispiel wird die Verwendung mehrerer SELECT-Anweisungen und mehrerer OUTPUT-Parameter dargestellt. OUTPUT-Parameter ermöglichen einer externen Prozedur, einem Batch oder mehreren Transact-SQL-Anweisungen während dem Ausführen der Prozedur den Zugriff auf einen Satz von Werten.

USE AdventureWorks2008R2;
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

Führen Sie uspGetList aus, um eine Liste der Adventure Works-Produkte (Bikes) zurückzugeben, die weniger als 700 $ kosten. Die OUTPUT-Parameter @Cost und @ComparePrices werden mit einer Ablaufsteuerungssprache verwendet, um eine Meldung an das 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

Verweis

Konzepte

Andere Ressourcen