Хранимые процедуры в AdventureWorks

Статья
12/15/2008

Образец базы данных OLTP AdventureWorks содержит несколько хранимых процедур Transact-SQL. Примеры хранимых процедур CLR см. в разделе Примеры программирования в среде CLR.

Хранимые процедуры CLR

В следующей таблице содержится список образцов доступных хранимых процедур CLR. Дополнительные сведения об использовании хранимых процедур CLR см. в разделе CLR Stored Procedures.

Образец	Описание
AdventureWorks Cycles CLR Layer	Хранимая процедура на языке C#, которая на входе получает данные xml и вставляет их в столбцы таблицы Person.Contact.
Обработка больших объектов	Демонстрирует использование хранимых процедур среды CLR и вызов хранимых процедур Transact-SQL из хранимых процедур среды CLR.

Хранимые процедуры Transact-SQL

В следующей таблице содержится список хранимых процедур Transact-SQL, которые содержатся в образце базы данных OLTP AdventureWorks. Дополнительные сведения о хранимых процедурах Transact-SQL см. в разделе Основные сведения о хранимых процедурах.

Хранимая процедура	Описание	Входные параметры
dbo.uspGetBillOfMaterials	Использует рекурсивный запрос (обобщенное табличное выражение), чтобы сформировать многоуровневую ведомость материалов: все компоненты уровня 1 сборки 0, все компоненты уровня 2 сборки 1 и так далее.	@StartProductIDint @CheckDatedatetime
dbo.uspGetEmployeeManagers	Использует рекурсивный запрос (обобщенное табличное выражение) для возврата прямых и косвенных менеджеров указанного служащего.	@EmployeeIDint
dbo.uspGetManagerEmployees	Использует рекурсивный запрос (обобщенное табличное выражение) для возврата прямых и косвенных служащих указанного менеджера.	@ManagerIDint
dbo.uspLogError	Записывает в таблицу dbo.ErrorLog сведения об ошибке, которая вызвала переход на блок CATCH конструкции TRY...CATCH. Эта процедура должна выполняться внутри блока CATCH, иначе возврат будет произведен без сведений об ошибке.	@ErrorLogIDint = 0 OUTPUT
dbo.uspPrintError	Выводит на печать сведения об ошибке, которая вызвала переход на блок CATCH конструкции TRY...CATCH. Эта процедура должна выполняться внутри блока CATCH, иначе возврат не будет содержать сведений об ошибке, выводимых на печать.	Нет
dbo.uspGetWhereUsedProductID	Использует рекурсивный запрос (обобщенное табличное выражение), чтобы возвратить все сборки продукта, которые используют указанный компонент продукта. Например, возвращает все велосипеды, в которых используется определенный тип колеса или краски.	@StartProductIDint @CheckDatedatetime
uspUpdateEmployeeHireInfo	Обновляет таблицу Employee и производит вставку в таблицу новой строки EmployeePayHistory со значениями, указанными во входных параметрах.	@EmployeeIDint @Titlenvarchar(50) @HireDatedatetime @RateChangeDatedatetime @Ratemoney @PayFrequencytinyint @CurrentFlagdbo.Flag
uspUpdateEmployeeLogin	Обновляет таблицу Employee значениями, переданными во входных параметрах, для указанного значения EmployeeID.	@EmployeeID int @ManagerIDint @LoginIDnvarchar(256) @Titlenvarchar(50) @HireDatedatetime @CurrentFlagdbo.Flag
uspUpdateEmployeePersonalInfo	Обновляет таблицу Employee значениями, переданными во входных параметрах, для указанного значения EmployeeID.	@EmployeeID int @NationalIDNumbernvarchar(15) @BirthDatedatetime @MaritalStatusnchar(1) @Gendernchar(1)

Примеры

A. Использование dbo.uspGetBillOfMaterials

В следующем примере выполняется хранимая процедура uspgetBillOfMaterials. Процедура возвращает иерархический список компонентов, используемых в производстве велосипеда Road-550-W Yellow, 44 (ProductID``800).

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

Б. Использование dbo.uspGetEmployeeManagers

В следующем примере выполняется хранимая процедура uspGetEmployeeManagers. Процедура возвращает иерархический список прямых и косвенных менеджеров для EmployeeID 50.

USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 50;

В. Использование dbo.uspGetManagerEmployees

В следующем примере выполняется хранимая процедура uspGetManagerEmployees. Процедура возвращает иерархический список прямых и косвенных подчиненных ManagerID 140.

USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 140;

Г. Использование dbo.uspGetWhereUsedProductID

В следующем примере выполняется хранимая процедура usp_getWhereUsedProductID. Эта процедура возвращает все продукты, использующие переднее дорожное колесо ML (ProductID 819)

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

Д. Использование HumanResources.uspUpdateEmployeeHireInfo

В следующем примере выполняется хранимая процедура uspUpdateEmployeeHireInfo. Эта процедура обновляет столбцы Title, HireDate и Current Flag в таблице Employee для указанных EmployeeID и добавляет новую строку в таблицу EmployeePayHistory со значениями для EmployeeID, RateChangeDate, Rate и PayFrequency. Должны быть указаны все значения параметров.

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

Е. Использование HumanResources.uspUpdateEmployeeLogin

В следующем примере выполняется хранимая процедура uspUpdateEmployeeLogin. Процедура обновляет столбцы ManagerID, LoginID, Title, HireDate и Current Flag в таблице Employee для EmployeeID 6. Должны быть указаны все значения параметров.

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 ;

Ж. Использование HumanResources.uspUpdateEmployeePersonalInfo

В следующем примере выполняется хранимая процедура uspUpdateEmployeePersonalInfo. Процедура обновляет столбцы NationalIDNumber, BirthDate, MaritalStatue и Gender в таблице Employee для EmployeeID 6. Должны быть указаны все значения параметров.

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

З. Использование dbo.uspLogError

В следующем примере делается попытка удалить продукт Mountain-400-W Silver, 38 (ProductID 980) из таблицы Production.Product. Ограничение внешнего ключа для таблицы не позволяет успешно выполнить операцию удаления, и ошибка нарушения ограничения передает управление блоку CATCH. Код внутри блока CATCH проверяет наличие любых активных транзакций и производит их откат перед выполнением хранимой процедуры uspLogError. Эта процедура заносит сведения об ошибке в таблицу ErrorLog и возвращает идентификатор ErrorLogID вставленной строки в аргументе @ErrorLogID OUTPUT. Аргумент @ErrorLogID имеет значение 0 по умолчанию. В этом случае запрашивается таблица ErrorLog, чтобы отобразить результаты выполнения хранимой процедуры.

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;

И. Использование dbo.uspPrintError

В следующем примере делается попытка удалить продукт Mountain-400-W Silver, 38 (ProductID``980) из таблицы Production.Product. Ограничение внешнего ключа для таблицы не позволяет успешно выполнить операцию удаления, и ошибка нарушения ограничения передает управление блоку CATCH. Код внутри блока CATCH выполняет хранимую процедуру uspPrintError. Эта процедура выводит сведения об ошибке на печать.

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

См. также

Другие ресурсы

Объекты SQL Server в AdventureWorks
CREATE PROCEDURE (Transact-SQL)
Образцы ядра СУБД SQL Server
TRY...CATCH (Transact-SQL)

Справка и поддержка

Получение помощи по SQL Server 2005

Поделиться через