Procedimientos almacenados de AdventureWorks
La base de datos OLTP de ejemplo AdventureWorks incluye varios procedimientos almacenados de Transact-SQL. Los ejemplos de procedimientos almacenados de Common Language Runtime (CLR) se pueden descargar de la página principal que proporciona ejemplos y proyectos de la comunidad de Microsoft SQL Server.
Procedimientos almacenados de CLR
En la tabla siguiente se enumeran los ejemplos de procedimientos almacenados de CLR disponibles. Para obtener más información sobre los procedimientos almacenados de CLR, vea Procedimientos almacenados de CLR.
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. |
Procedimientos almacenados de Transact-SQL
En la tabla siguiente se enumeran los procedimientos almacenados de Transact-SQL que se incluyen en la base de datos OLTP de ejemplo AdventureWorks. Para obtener más información sobre los procedimientos almacenados de Transact-SQL, vea Descripción de los procedimientos almacenados.
Procedimiento almacenado |
Descripción |
Parámetros de entrada |
|---|---|---|
dbo.uspGetBillOfMaterials |
Utiliza una consulta recursiva (expresión de tabla común) para generar una lista de materiales multinivel: todos los componentes de nivel 1 con un nivel 0 de ensamblado, todos los componentes de nivel 2 con un nivel 1 de ensamblado, etc. |
@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 (ProductID800).
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. Deben 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 valor predeterminado del parámetro @ErrorLogID es 0. La tabla ErrorLog se consulta a continuación 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 (ProductID980) 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