Gespeicherte Prozeduren in AdventureWorks

Die AdventureWorks-OLTP-Beispieldatenbank umfasst verschiedene gespeicherte Transact-SQL-Prozeduren. Beispiele für CLR-gespeicherte Prozeduren (Common Language Runtime) können Sie unter Microsoft SQL Server Samples and Community Projects herunterladen.

CLR-gespeicherte Prozeduren

In der folgenden Tabelle sind Beispiele für die verfügbaren CLR-gespeicherten Prozeduren aufgeführt. Weitere Informationen zu CLR-gespeicherten Prozeduren finden Sie unter CLR-gespeicherte Prozeduren.

Beispiel	Beschreibung
AdventureWorks Cycles CLR Layer	Eine C#-basierte gespeicherte Prozedur, die xml-Daten als Eingabe erfordert und die Daten in Spalten der Person.Contact-Tabelle einfügt.

Gespeicherte Prozeduren in Transact-SQL

In der folgenden Tabelle sind die Transact-SQL-gespeicherten Prozeduren aufgelistet, die in der AdventureWorks-OLTP-Beispieldatenbank enthalten sind. Weitere Informationen zu gespeicherten Prozeduren in Transact-SQL finden Sie unter Grundlegendes zu gespeicherten Prozeduren.

Gespeicherte Prozedur	Beschreibung	Eingabeparameter
dbo.uspGetBillOfMaterials	Verwendet eine rekursive Abfrage (allgemeiner Tabellenausdruck), um eine mehrstufige Stückliste zu generieren: alle Bauteile der Ebene 1 einer Baugruppe der Ebene 0 und alle Bauteile der Ebene 2 einer Baugruppe der Ebene 1 usw.	@StartProductIDint @CheckDatedatetime
dbo.uspGetEmployeeManagers	Verwendet eine rekursive Abfrage (allgemeiner Tabellenausdruck), um die direkten und indirekten Vorgesetzten eines bestimmten Mitarbeiters zurückzugeben.	@EmployeeIDint
dbo.uspGetManagerEmployees	Verwendet eine rekursive Abfrage (allgemeiner Tabellenausdruck), um die direkten und indirekten Mitarbeiter eines bestimmten Vorgesetzten zurückzugeben.	@ManagerIDint
dbo.uspLogError	Protokolliert Fehlerinformationen in der dbo.ErrorLog-Tabelle zu dem Fehler, der dazu geführt hat, dass die Ausführung zum CATCH-Block eines TRY...CATCH-Konstrukts gesprungen ist. Diese Prozedur sollte aus dem Bereich eines CATCH-Blocks heraus ausgeführt werden, da andernfalls keine Fehlerinformationen eingefügt werden.	@ErrorLogIDint = 0 OUTPUT
dbo.uspPrintError	Druckt Fehlerinformationen zu dem Fehler, der dazu geführt hat, dass die Ausführung zum CATCH-Block eines TRY...CATCH-Konstrukts gesprungen ist. Diese Prozedur sollte aus dem Bereich eines CATCH-Blocks heraus ausgeführt werden, da andernfalls keine Fehlerinformationen gedruckt werden.	Keine
dbo.uspGetWhereUsedProductID	Verwendet eine rekursive Abfrage (allgemeiner Tabellenausdruck), um alle Produktassemblys zurückzugeben, in dem das bestimmte Produktbauteil verwendet wird. So werden beispielsweise alle Fahrräder zurückgegeben, für die ein bestimmtes Rad oder ein bestimmter Lacktyp verwendet wurde.	@StartProductIDint @CheckDatedatetime
uspUpdateEmployeeHireInfo	Aktualisiert die Employee-Tabelle und fügt eine neue Zeile in die EmployeePayHistory-Tabelle mithilfe der in den Eingabeparametern angegebenen Werten ein.	@EmployeeIDint @Titlenvarchar(50) @HireDatedatetime @RateChangeDatedatetime @Ratemoney @PayFrequencytinyint @CurrentFlagdbo.Flag
uspUpdateEmployeeLogin	Aktualisiert die Employee-Tabelle anhand der in den Eingabeparametern angegebenen Werte für die angegebene EmployeeID-Spalte.	@EmployeeID int @ManagerIDint @LoginIDnvarchar(256) @Titlenvarchar(50) @HireDatedatetime @CurrentFlagdbo.Flag
uspUpdateEmployeePersonalInfo	Aktualisiert die Employee-Tabelle anhand der in den Eingabeparametern angegebenen Werte für die angegebene EmployeeID-Spalte.	@EmployeeID int @NationalIDNumbernvarchar(15) @BirthDatedatetime @MaritalStatusnchar(1) @Gendernchar(1)

Beispiele

A. Verwenden von dbo.uspGetBillOfMaterials

Im folgenden Beispiel wird die gespeicherte Prozedur uspgetBillOfMaterials ausgeführt. Die Prozedur gibt eine hierarchische Liste der Bauteile zurück, die zur Fertigung des Produkts Road-550-W Yellow, 44 (ProductID800) verwendet werden.

USE AdventureWorks;
GO
DECLARE @CheckDate DATETIME;
SET @CheckDate = GETDATE();
EXEC [AdventureWorks].[dbo].[uspGetBillOfMaterials] 800, @CheckDate;

B. Verwenden von dbo.uspGetEmployeeManagers

Im folgenden Beispiel wird die gespeicherte Prozedur uspGetEmployeeManagers ausgeführt. Die Prozedur gibt eine hierarchische Liste der direkten und indirekten Vorgesetzten von EmployeeID 50 zurück.

USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 50;

C. Verwenden von dbo.uspGetManagerEmployees

Im folgenden Beispiel wird die gespeicherte Prozedur uspGetManagerEmployees ausgeführt. Die Prozedur gibt eine hierarchische Liste der direkten und indirekten Mitarbeiter zurück, die ManagerID 140 unterstellt sind.

USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 140;

D. Verwenden von dbo.uspGetWhereUsedProductID

Im folgenden Beispiel wird die gespeicherte Prozedur usp_getWhereUsedProductID ausgeführt. Die Prozedur gibt alle Produkte zurück, in denen das Produkt ML Road Front Wheel (ProductID 819) verwendet wird.

USE AdventureWorks
GO
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;

E. Verwenden von HumanResources.uspUpdateEmployeeHireInfo

Im folgenden Beispiel wird die gespeicherte Prozedur uspUpdateEmployeeHireInfo ausgeführt. Die Prozedur aktualisiert die Spalten Title, HireDate und Current Flag in der Employee-Tabelle für die angegebene EmployeeID-Spalte und fügt der EmployeePayHistory-Tabelle eine neue Zeile mit Werten für EmployeeID, RateChangeDate, Rate und PayFrequency hinzu. Es müssen alle Parameterwerte angegeben werden.

USE AdventureWorks;
GO
EXEC [HumanResources].[uspUpdateEmployeeHireInfo]
    @EmployeeID = 109, 
    @Title = N'President', 
    @HireDate = '19980513',
    @RateChangeDate = '20041208', 
    @Rate = 50.00, 
    @PayFrequency = 1, 
    @CurrentFlag = 1;

F. Verwenden von HumanResources.uspUpdateEmployeeLogin

Im folgenden Beispiel wird die gespeicherte Prozedur uspUpdateEmployeeLogin ausgeführt. Die Prozedur aktualisiert die Spalten ManagerID, LoginID, Title, HireDate und Current Flag in der Employee-Tabelle für die EmployeeID 6-Spalte. Es müssen alle Parameterwerte angegeben werden.

USE AdventureWorks;
GO
DECLARE @HireDate datetime;
SET @HireDate = CONVERT(DATETIME,'19990619',101);
EXEC HumanResources.uspUpdateEmployeeLogin
    @EmployeeID = 6, 
    @ManagerID = 273,
    @LoginID = N'adventure-works\david01',
    @Title = N'Marketing Vice President', 
    @HireDate = @HireDate,
    @CurrentFlag = 1 ;

G. Verwenden von HumanResources.uspUpdateEmployeePersonalInfo

Im folgenden Beispiel wird die gespeicherte Prozedur uspUpdateEmployeePersonalInfo ausgeführt. Die Prozedur aktualisiert die Spalten NationalIDNumber, BirthDate, MaritalStatue und Gender in der Employee-Tabelle für EmployeeID 6. Es müssen alle Parameterwerte angegeben werden.

USE AdventureWorks;
GO
EXEC HumanResources.uspUpdateEmployeePersonalInfo
    @EmployeeID = 6, 
    @NationalIDNumber = N'123-45-6789',
    @BirthDate = '19651030',
    @MaritalStatus = N'S', 
    @Gender = N'M';
GO

H. Verwenden von dbo.uspLogError

Im folgenden Beispiel wird versucht, das Produkt Mountain-400-W Silver, 38 (ProductID 980) aus der Production.Product-Tabelle zu löschen. Eine für die Tabelle geltende FOREIGN KEY-Einschränkung lässt den Löschvorgang nicht zu. Der aufgrund der Einschränkungsverletzung aufgetretene Fehler übergibt die Kontrolle an den CATCH-Block. Mithilfe des Codes im CATCH-Block wird zuerst überprüft, ob aktive Transaktionen vorliegen, dann wird ein Rollback der Transaktionen ausgeführt und anschließend die gespeicherte Prozedur uspLogError ausgeführt. Diese Prozedur gibt die Fehlerinformationen in die ErrorLog-Tabelle ein und gibt ErrorLogID der in den @ErrorLogID OUTPUT-Parameter eingefügten Zeile zurück. Der Standardwert des Parameters @ErrorLogID ist 0. Anschließend wird die Tabelle ErrorLog abgefragt, um die Ergebnisse der gespeicherten Prozedur anzuzeigen.

USE AdventureWorks;
GO
BEGIN TRY
    BEGIN TRANSACTION;
    DELETE FROM Production.Product
        WHERE ProductID = 980;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;
    DECLARE @ErrorLogID INT;
    EXECUTE dbo.uspLogError @ErrorLogID = @ErrorLogID OUTPUT;
END CATCH;
GO
--View the results of the uspLogError stored procedure
SELECT * FROM dbo.ErrorLog;

I. Verwenden von dbo.uspPrintError

Im folgenden Beispiel wird versucht, das Produkt Mountain-400-W Silver, 38 (ProductID980) aus der Production.Product-Tabelle zu löschen. Eine für die Tabelle geltende FOREIGN KEY-Einschränkung lässt den Löschvorgang nicht zu. Der aufgrund der Einschränkungsverletzung aufgetretene Fehler übergibt die Kontrolle an den CATCH-Block. Mithilfe des Codes im CATCH-Block wird die gespeicherte Prozedur uspPrintError ausgeführt. Diese Prozedur druckt die Fehlerinformationen.

USE AdventureWorks;
GO
BEGIN TRY
    DELETE FROM Production.Product
        WHERE ProductID = 980;
END TRY
BEGIN CATCH
    EXECUTE dbo.uspPrintError;
END CATCH;
GO

Siehe auch

Aufgaben

Beispiele für SQL Server Database Engine

Verweis

CREATE PROCEDURE (Transact-SQL)

TRY...CATCH (Transact-SQL)

Andere Ressourcen

SQL Server-Objekte in AdventureWorks