Gespeicherte Prozeduren in AdventureWorks

Artikel
12/15/2008

Die AdventureWorks-OLTP-Beispieldatenbank umfasst verschiedene gespeicherte Prozeduren in Transact-SQL. Beispiele für CLR-gespeicherte Prozeduren (Common Language Runtime) stehen in den Beispiele für die CLR-Programmierbarkeit zur Verfügung.

CLR-gespeicherte Prozeduren

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

Beispiel	Beschreibung
AdventureWorks Cycles CLR Layer	Eine C#-basierte gespeicherte Prozedur, die xml-Daten als Eingabe nimmt und in Daten in der Person.Contact-Tabelle einfügt.
LOB-Behandlung (Large Object)	Veranschaulicht die Verwendung CLR-gespeicherter Prozeduren und das Aufrufen von Transact-SQL-gespeicherten Prozeduren aus CLR-gespeicherten Prozeduren heraus.

Gespeicherte Prozeduren in Transact-SQL

In der folgenden Tabelle werden 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 (ProductID``800) 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 @ErrorLogID-Parameter weist den Standardwert 0 auf. Die ErrorLog-Tabelle wird dann 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 (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 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

Andere Ressourcen

SQL Server-Objekte in AdventureWorks
CREATE PROCEDURE (Transact-SQL)
Beispiele für SQL Server Database Engine
TRY...CATCH (Transact-SQL)

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Freigeben über