AdventureWorks에 있는 저장 프로시저
AdventureWorks 예제 OLTP 데이터베이스에는 다양한 Transact-SQL 저장 프로시저가 포함되어 있습니다. CLR(공용 언어 런타임) 저장 프로시저의 예는 CLR 프로그래밍 기능 예제에서 사용할 수 있습니다.
CLR 저장 프로시저
다음 표에서는 사용할 수 있는 CLR 저장 프로시저의 예제를 설명합니다. CLR 저장 프로시저에 대한 자세한 내용은 CLR Stored Procedures를 참조하십시오.
예제 | 설명 |
---|---|
xml 데이터를 입력으로 사용하여 Person.Contact 테이블의 열에 데이터를 삽입하는 C# 기반 저장 프로시저 |
|
CLR 저장 프로시저를 사용하는 방법 및 CLR 저장 프로시저에서 Transact-SQL 저장 프로시저를 호출하는 방법을 보여 줍니다. |
Transact-SQL 저장 프로시저
다음 표에서는 AdventureWorks 예제 OLTP 데이터베이스에 포함되어 있는 Transact-SQL 저장 프로시저를 설명합니다. Transact-SQL 저장 프로시저에 대한 자세한 내용은 저장 프로시저 이해를 참조하십시오.
저장 프로시저 | 설명 | 입력 매개 변수 |
---|---|---|
dbo.uspGetBillOfMaterials |
재귀 쿼리(공용 테이블 식)를 사용하여 수준 0 어셈블리의 모든 수준 1 구성 요소, 수준 1 어셈블리의 모든 수준 2 구성 요소와 같은 다중 수준 제품 구성 정보(BOM)를 생성합니다. |
@StartProductIDint @CheckDatedatetime |
dbo.uspGetEmployeeManagers |
재귀 쿼리(공용 테이블 식)를 사용하여 지정된 직원의 직속 관리자 및 관련 부서 관리자를 반환합니다. |
@EmployeeIDint |
dbo.uspGetManagerEmployees |
재귀 쿼리(공용 테이블 식)를 사용하여 지정된 관리자의 직속 부하 직원 및 관련 부서 직원을 반환합니다. |
@ManagerIDint |
dbo.uspLogError |
TRY...CATCH 구문의 CATCH 블록으로 이동시킨 오류에 대한 정보를 dbo.ErrorLog 테이블에 기록합니다. 이 프로시저는 CATCH 블록 내에서 실행되어야 합니다. 그렇지 않으면 오류 정보를 삽입하지 않고 반환됩니다. |
@ErrorLogIDint = 0 OUTPUT |
dbo.uspPrintError |
TRY...CATCH 구문의 CATCH 블록으로 이동시킨 오류에 대한 정보를 인쇄합니다. 이 프로시저는 CATCH 블록 내에서 실행되어야 합니다. 그렇지 않으면 오류 정보를 인쇄하지 않고 반환됩니다. |
없음 |
dbo.uspGetWhereUsedProductID |
재귀 쿼리(공용 테이블 식)를 사용하여 지정된 제품 구성 요소를 사용하는 모든 제품 어셈블리를 반환합니다. 예를 들어 특정 바퀴 또는 페인트 유형을 사용하는 모든 자전거를 반환합니다. |
@StartProductIDint @CheckDatedatetime |
uspUpdateEmployeeHireInfo |
Employee 테이블을 업데이트하고 입력 매개 변수에 지정된 값으로 EmployeePayHistory 테이블에 새 행을 삽입합니다. |
@EmployeeIDint @Titlenvarchar(50) @HireDatedatetime @RateChangeDatedatetime @Ratemoney @PayFrequencytinyint @CurrentFlagdboFlag |
uspUpdateEmployeeLogin |
지정한 EmployeeID의 입력 매개 변수에 지정된 값으로 Employee 테이블을 업데이트합니다. |
@EmployeeID int @ManagerIDint @LoginIDnvarchar(256) @Titlenvarchar(50) @HireDatedatetime @CurrentFlagdboFlag |
uspUpdateEmployeePersonalInfo |
지정한 EmployeeID의 입력 매개 변수에 지정된 값으로 Employee 테이블을 업데이트합니다. |
@EmployeeID int @NationalIDNumbernvarchar(15) @BirthDatedatetime @MaritalStatusnchar(1) @Gendernchar(1) |
예
1. 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;
2. dbo.uspGetEmployeeManagers 사용
다음 예에서는 uspGetEmployeeManagers
저장 프로시저를 실행합니다. 이 프로시저는 EmployeeID 50
의 직속 관리자 및 관련 부서 관리자의 계층적 목록을 반환합니다.
USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 50;
3. dbo.uspGetManagerEmployees 사용
다음 예에서는 uspGetManagerEmployees
저장 프로시저를 실행합니다. 이 프로시저는 ManagerID 140
에게 보고하는 직속 부하 직원 및 관련 부서 직원의 계층적 목록을 반환합니다.
USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 140;
4. dbo.uspGetWhereUsedProductID 사용
다음 예에서는 usp
_getWhereUsedProductID
저장 프로시저를 실행합니다. 이 프로시저는 ML Road Front Wheel 제품(ProductID 819
)을 사용하는 모든 제품을 반환합니다.
USE AdventureWorks
GO
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
5. HumanResources.uspUpdateEmployeeHireInfo 사용
다음 예에서는 uspUpdateEmployeeHireInfo
저장 프로시저를 실행합니다. 이 프로시저는 지정된 EmployeeID
에 대해 Employee
테이블의 Title
, HireDate
및 Current Flag
열을 업데이트하고 EmployeeID
, RateChangeDate
, Rate
및 PayFrequency
값으로 EmployeePayHistory
테이블에 새 행을 삽입합니다. 모든 매개 변수 값을 지정해야 합니다.
USE AdventureWorks;
GO
EXEC [HumanResources].[uspUpdateEmployeeHireInfo]
@EmployeeID = 109,
@Title = N'President',
@HireDate = '19980513',
@RateChangeDate = '20041208',
@Rate = 50.00,
@PayFrequency = 1,
@CurrentFlag = 1;
6. HumanResources.uspUpdateEmployeeLogin 사용
다음 예에서는 uspUpdateEmployeeLogin
저장 프로시저를 실행합니다. 이 프로시저는 EmployeeID 6
에 대해 Employee
테이블의 ManagerID, LoginID, Title
, HireDate
및 Current Flag
열을 업데이트합니다. 모든 매개 변수 값을 지정해야 합니다.
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 ;
7. HumanResources.uspUpdateEmployeePersonalInfo 사용
다음 예에서는 uspUpdateEmployeePersonalInfo
저장 프로시저를 실행합니다. 이 프로시저는 EmployeeID 6
에 대해 Employee
테이블의 NationalIDNumber
, BirthDate
, MaritalStatue
및 Gender
열을 업데이트합니다. 모든 매개 변수 값을 지정해야 합니다.
USE AdventureWorks;
GO
EXEC HumanResources.uspUpdateEmployeePersonalInfo
@EmployeeID = 6,
@NationalIDNumber = N'123-45-6789',
@BirthDate = '19651030',
@MaritalStatus = N'S',
@Gender = N'M';
GO
8. dbo.uspLogError 사용
다음 예에서는 Production.Product
테이블에서 Mountain-400-W Silver, 38 제품(ProductID 980
)을 삭제합니다. 이 테이블의 FOREIGN KEY 제약 조건으로 인해 삭제 작업이 실패하고 제약 조건 위반 오류가 발생하여 CATCH
블록으로 제어 권한이 이동합니다. CATCH
블록 내부에 있는 코드에서는 먼저 활성 트랜잭션이 있는지 확인하여 uspLogError
저장 프로시저를 실행하기 전에 롤백합니다. 이 프로시저는 ErrorLog
테이블에 오류 정보를 입력하고 @ErrorLogID OUTPUT
매개 변수에 삽입된 행의 ErrorLogID
를 반환합니다. @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;
9. dbo.uspPrintError 사용
다음 예에서는 Production.Product
테이블에서 Mountain-400-W Silver, 38 제품(ProductID``980
)을 삭제합니다. 이 테이블의 FOREIGN KEY 제약 조건으로 인해 삭제 작업이 실패하고 제약 조건 위반 오류가 발생하여 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
참고 항목
관련 자료
AdventureWorks에 있는 SQL Server 개체
CREATE PROCEDURE(Transact-SQL)
SQL Server 데이터베이스 엔진 예제
TRY...CATCH(Transact-SQL)