Stored Procedures in AdventureWorks
The AdventureWorks sample OLTP database includes several Transact-SQL stored procedures. Examples of common language runtime (CLR) stored procedures are available in CLR Programmability Samples.
CLR Stored Procedures
The following table lists the CLR stored procedure samples that are available. For more information about CLR stored procedures, see CLR Stored Procedures.
| Sample | Description |
|---|---|
A C#-based stored procedure that takes xml data as its input and inserts the data into columns in the Person.Contact table. |
|
Demonstrates using CLR store procedures and calling Transact-SQL stored procedures from CLR stored procedures. |
Transact-SQL Stored Procedures
The following table lists the Transact-SQL stored procedures that are included in the AdventureWorks sample OLTP database. For more information about Transact-SQL stored procedures, see Understanding Stored Procedures.
| Stored procedure | Description | Input parameters |
|---|---|---|
dbo.uspGetBillOfMaterials |
Uses a recursive query (common table expression) to generate a multilevel Bill of Material: all level 1 components of a level 0 assembly, all level 2 components of a level 1 assembly, and so on. |
@StartProductIDint @CheckDatedatetime |
dbo.uspGetEmployeeManagers |
Uses a recursive query (common table expression) to return the direct and indirect managers of the specified employee. |
@EmployeeIDint |
dbo.uspGetManagerEmployees |
Uses a recursive query (common table expression) to return the direct and indirect employees of the specified manager. |
@ManagerIDint |
dbo.uspLogError |
Logs error information in the dbo.ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. This procedure should be executed from within the scope of a CATCH block; otherwise, it will return without inserting error information. |
@ErrorLogIDint = 0 OUTPUT |
dbo.uspPrintError |
Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. This procedure should be executed from within the scope of a CATCH block; otherwise, it will return without printing any error information. |
None |
dbo.uspGetWhereUsedProductID |
Uses a recursive query (common table expression) to return all product assemblies that use the specified product component. For example, return all bicycles that use a specific wheel or type of paint. |
@StartProductIDint @CheckDatedatetime |
uspUpdateEmployeeHireInfo |
Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters. |
@EmployeeIDint @Titlenvarchar(50) @HireDatedatetime @RateChangeDatedatetime @Ratemoney @PayFrequencytinyint @CurrentFlagdbo.Flag |
uspUpdateEmployeeLogin |
Updates the Employee table with the values specified in the input parameters for the specified EmployeeID. |
@EmployeeID int @ManagerIDint @LoginIDnvarchar(256) @Titlenvarchar(50) @HireDatedatetime @CurrentFlagdbo.Flag |
uspUpdateEmployeePersonalInfo |
Updates the Employee table with the values specified in the input parameters for the specified EmployeeID. |
@EmployeeID int @NationalIDNumbernvarchar(15) @BirthDatedatetime @MaritalStatusnchar(1) @Gendernchar(1) |
Examples
A. Using dbo.uspGetBillOfMaterials
The following example runs the uspgetBillOfMaterials stored procedure. The procedure returns a hierarchical list of components used to manufacture the Road-550-W Yellow, 44 product (ProductID``800).
USE AdventureWorks;
GO
DECLARE @CheckDate DATETIME;
SET @CheckDate = GETDATE();
EXEC [AdventureWorks].[dbo].[uspGetBillOfMaterials] 800, @CheckDate;
B. Using dbo.uspGetEmployeeManagers
The following example runs the uspGetEmployeeManagers stored procedure. The procedure returns a hierarchical list of direct and indirect managers for EmployeeID 50.
USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 50;
C. Using dbo.uspGetManagerEmployees
The following example runs the uspGetManagerEmployees stored procedure. The procedure returns a hierarchical list of direct and indirect employees reporting to ManagerID 140.
USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 140;
D. Using dbo.uspGetWhereUsedProductID
The following example runs the usp_getWhereUsedProductID stored procedure. The procedure returns return all products that use the product ML Road Front Wheel (ProductID 819)
USE AdventureWorks
GO
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
E. Using HumanResources.uspUpdateEmployeeHireInfo
The following example runs the uspUpdateEmployeeHireInfo stored procedure. The procedure updates the Title, HireDate, and Current Flag columns in the Employee table for the specified EmployeeID and inserts a new row in the EmployeePayHistory table with values for EmployeeID, RateChangeDate, Rate and PayFrequency. All parameter values must be specified.
USE AdventureWorks;
GO
EXEC [HumanResources].[uspUpdateEmployeeHireInfo]
@EmployeeID = 109,
@Title = N'President',
@HireDate = '19980513',
@RateChangeDate = '20041208',
@Rate = 50.00,
@PayFrequency = 1,
@CurrentFlag = 1;
F. Using HumanResources.uspUpdateEmployeeLogin
The following example runs the uspUpdateEmployeeLogin stored procedure. The procedure updates the ManagerID, LoginID, Title, HireDate, and Current Flag columns in the Employee table for EmployeeID 6. All parameter values must be specified.
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. Using HumanResources.uspUpdateEmployeePersonalInfo
The following example runs the uspUpdateEmployeePersonalInfo stored procedure. The procedure updates the NationalIDNumber, BirthDate, MaritalStatue, and Gender columns in the Employee table for EmployeeID 6. All parameter values must be specified.
USE AdventureWorks;
GO
EXEC HumanResources.uspUpdateEmployeePersonalInfo
@EmployeeID = 6,
@NationalIDNumber = N'123-45-6789',
@BirthDate = '19651030',
@MaritalStatus = N'S',
@Gender = N'M';
GO
H. Using dbo.uspLogError
The following example tries to delete the product Mountain-400-W Silver, 38 (ProductID 980) from the Production.Product table. A FOREIGN KEY constraint on the table does not allow the delete operation to succeed, and the constraint violation error passes control to the CATCH block. The code inside the CATCH block first checks for any active transactions and rolls them back before it executes the uspLogError stored procedure. This procedure enters the error information in the ErrorLog table and returns the ErrorLogID of the row inserted into the @ErrorLogID OUTPUT parameter. The @ErrorLogID parameter has a default value of 0. The ErrorLog table is then queried to view the results of the stored procedure.
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. Using dbo.uspPrintError
The following example tries to delete the product Mountain-400-W Silver, 38 (ProductID``980) from the Production.Product table. A FOREIGN KEY constraint on the table does not allow the delete operation to succeed, and the constraint violation error passes control to the CATCH block. The code inside the CATCH block executes the uspPrintError stored procedure. This procedure prints the error information.
USE AdventureWorks;
GO
BEGIN TRY
DELETE FROM Production.Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
EXECUTE dbo.uspPrintError;
END CATCH;
GO
See Also
Other Resources
SQL Server Objects in AdventureWorks
CREATE PROCEDURE (Transact-SQL)
SQL Server Database Engine Samples
TRY...CATCH (Transact-SQL)