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.

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.

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)

Help and Information

Getting SQL Server 2005 Assistance