Article
10/01/2010

Stored Procedures in AdventureWorks

The AdventureWorks sample OLTP database includes several Transact-SQL stored procedures. Examples of common language runtime (CLR) stored procedures can be downloaded from the Microsoft SQL Server Samples and Community Projects home page.

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
AdventureWorks Cycles CLR Layer	A C#-based stored procedure that takes xml data as its input and inserts the data into columns in the Person.Contact table.

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 (ProductID800).

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

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

Share via