Procedimientos almacenados de AdventureWorks

Artículo
12/15/2008

La base de datos OLTP de ejemplo AdventureWorks incluye varios procedimientos almacenados Transact-SQL. Los ejemplos de los procedimientos almacenados CLR (Common Language Runtime) se encuentran en Ejemplos de programación de CLR.

Procedimientos almacenados CLR

En la tabla siguiente se enumeran los ejemplos de procedimientos almacenados CLR disponibles. Para obtener más información sobre los procedimientos almacenados CLR, vea CLR Stored Procedures.

Ejemplo	Descripción
AdventureWorks Cycles CLR Layer	Procedimiento almacenado basado en C# que toma datos xml como entrada y los inserta en las columnas de la tabla Person.Contact.
Control de objetos grandes (LOB)	Demuestra cómo utilizar procedimientos almacenados CLR y llamar a procedimientos almacenados Transact-SQL desde procedimientos almacenados CLR.

Procedimientos almacenados Transact-SQL

En la tabla siguiente se enumeran los procedimientos almacenados Transact-SQL que se incluyen en la base de datos OLTP de ejemplo AdventureWorks. Para obtener más información sobre los procedimientos almacenados Transact-SQL, vea Descripción de los procedimientos almacenados.

Procedimiento almacenado	Descripción	Parámetros de entrada
dbo.uspGetBillOfMaterials	Usa una consulta recursiva (expresión de tabla común) para generar una lista de materiales de varios niveles: todos los componentes de nivel 1 de un ensamblado de nivel 0, todos los componentes de nivel 2 de un ensamblado de nivel 1, y así sucesivamente..	@StartProductIDint @CheckDatedatetime
dbo.uspGetEmployeeManagers	Utiliza una consulta recursiva (expresión de tabla común) para devolver los directores directos e indirectos del empleado especificado.	@EmployeeIDint
dbo.uspGetManagerEmployees	Utiliza una consulta recursiva (expresión de tabla común) para devolver los empleados directos e indirectos del director especificado.	@ManagerIDint
dbo.uspLogError	Registra información de error en la tabla dbo.ErrorLog sobre el error que ha dado lugar a que la ejecución salte a un bloque CATCH de un constructor TRY...CATCH. Este procedimiento debe ejecutarse desde el ámbito de un bloque CATCH; de lo contrario, regresará sin insertar información sobre el error.	@ErrorLogIDint = 0 OUTPUT
dbo.uspPrintError	Imprime información de error sobre el error que ha dado lugar a que la ejecución salte a un bloque CATCH de un constructor TRY...CATCH. Este procedimiento debe ejecutarse desde el ámbito de un bloque CATCH; de lo contrario, regresará sin imprimir información sobre el error.	Ninguno
dbo.uspGetWhereUsedProductID	Utiliza una consulta recursiva (expresión de tabla común) para devolver todos los ensamblados de producto que utilizan el componente de producto especificado. Por ejemplo, devuelve todas las bicicletas que utilizan una rueda específica o un tipo de pintura específico.	@StartProductIDint @CheckDatedatetime
uspUpdateEmployeeHireInfo	Actualiza la tabla Employee e inserta una fila nueva en la tabla EmployeePayHistory con los valores especificados en los parámetros de entrada.	@EmployeeIDint @Titlenvarchar(50) @HireDatedatetime @RateChangeDatedatetime @Ratemoney @PayFrequencytinyint @CurrentFlagdbo.Flag
uspUpdateEmployeeLogin	Actualiza la tabla Employee con los valores especificados en los parámetros de entrada para el EmployeeID especificado.	@EmployeeID int @ManagerIDint @LoginIDnvarchar(256) @Titlenvarchar(50) @HireDatedatetime @CurrentFlagdbo.Flag
uspUpdateEmployeePersonalInfo	Actualiza la tabla Employee con los valores especificados en los parámetros de entrada para el EmployeeID especificado.	@EmployeeID int @NationalIDNumbernvarchar(15) @BirthDatedatetime @MaritalStatusnchar(1) @Gendernchar(1)

Ejemplos

A. Usar dbo.uspGetBillOfMaterials

En el ejemplo siguiente se ejecuta el procedimiento almacenado uspgetBillOfMaterials. El procedimiento devuelve una lista jerárquica de los componentes que se utilizan para fabricar el producto Road-550-W Yellow, 44 (ProductID``800).

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

B. Usar dbo.uspGetEmployeeManagers

En el ejemplo siguiente se ejecuta el procedimiento almacenado uspGetEmployeeManagers. El procedimiento devuelve una lista jerárquica de los directores directos e indirectos para EmployeeID 50.

USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 50;

C. Usar dbo.uspGetManagerEmployees

En el ejemplo siguiente se ejecuta el procedimiento almacenado uspGetManagerEmployees. El procedimiento devuelve una lista jerárquica de los empleados directos e indirectos que informan a ManagerID 140.

USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 140;

D. Usar dbo.uspGetWhereUsedProductID

En el ejemplo siguiente se ejecuta el procedimiento almacenado usp_getWhereUsedProductID. El procedimiento devuelve todos los productos que utilizan el producto ML Road Front Wheel (ProductID 819)

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

E. Usar HumanResources.uspUpdateEmployeeHireInfo

En el ejemplo siguiente se ejecuta el procedimiento almacenado uspUpdateEmployeeHireInfo. El procedimiento actualiza las columnas Title, HireDate y Current Flag de la tabla Employee para el EmployeeID especificado e inserta una fila nueva en la tabla EmployeePayHistory con los valores para EmployeeID, RateChangeDate, Rate y PayFrequency. Debe especificarse los valores de todos los parámetros.

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

F. Usar HumanResources.uspUpdateEmployeeLogin

En el ejemplo siguiente se ejecuta el procedimiento almacenado uspUpdateEmployeeLogin. El procedimiento actualiza las columnas ManagerID, LoginID, Title, HireDate y Current Flag de la tabla Employee para EmployeeID 6. Deben especificarse los valores de todos los parámetros.

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. Usar HumanResources.uspUpdateEmployeePersonalInfo

En el ejemplo siguiente se ejecuta el procedimiento almacenado uspUpdateEmployeePersonalInfo. El procedimiento actualiza las columnas NationalIDNumber, BirthDate, MaritalStatue y Gender de la tabla Employee para EmployeeID 6. Deben especificarse los valores de todos los parámetros.

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

H. Usar dbo.uspLogError

En el ejemplo siguiente se intenta eliminar el producto Mountain-400-W Silver, 38 (ProductID 980) de la tabla Production.Product. Una restricción FOREIGN KEY de la tabla no permite que la operación de supresión se ejecute correctamente, y el error de infracción de la restricción pasa el control al bloque CATCH. El código del bloque CATCH comprueba primero si hay transacciones activas y las deshace antes de ejecutar el procedimiento almacenado uspLogError. Este procedimiento escribe la información de error en la tabla ErrorLog y devuelve el ErrorLogID de la fila insertada en el parámetro @ErrorLogID OUTPUT. El parámetro @ErrorLogID tiene el valor 0. A continuación se realiza una consulta de la tabla ErrorLog para ver los resultados del procedimiento almacenado.

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. Usar dbo.uspPrintError

En el ejemplo siguiente se intenta eliminar el producto Mountain-400-W Silver, 38 (ProductID``980) de la tabla Production.Product. Una restricción FOREIGN KEY de la tabla no permite que la operación de supresión se ejecute correctamente, y el error de infracción de la restricción pasa el control al bloque CATCH. El código del bloque CATCH ejecuta el procedimiento almacenado uspPrintError. Este procedimiento imprime la información de error.

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

Vea también

Otros recursos

Objetos de SQL Server en AdventureWorks
CREATE PROCEDURE (Transact-SQL)
Ejemplos de motor de base de datos de SQL Server
TRY...CATCH (Transact-SQL)

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Compartir a través de