아티클
09/27/2010

AdventureWorks에 있는 저장 프로시저

AdventureWorks 예제 OLTP 데이터베이스에는 다양한 Transact-SQL 저장 프로시저가 포함되어 있습니다. CLR(공용 언어 런타임) 저장 프로시저의 예제는 Microsoft SQL Server 예제 및 커뮤니티 프로젝트 홈 페이지에서 다운로드할 수 있습니다.

CLR 저장 프로시저

다음 표에서는 사용할 수 있는 CLR 저장 프로시저의 예제를 설명합니다. CLR 저장 프로시저에 대한 자세한 내용은 CLR 저장 프로시저를 참조하십시오.

예제	설명
AdventureWorks Cycles CLR 계층	xml 데이터를 입력으로 사용하여 Person.Contact 테이블의 열에 데이터를 삽입하는 C# 기반 저장 프로시저

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 제품(ProductID800)을 제조하는 데 사용된 구성 요소의 계층적 목록을 반환합니다.

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 제품(ProductID980)을 삭제합니다. 이 테이블의 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

참고 항목

태스크

SQL Server 데이터베이스 엔진 예제

참조

CREATE PROCEDURE(Transact-SQL)

TRY...CATCH(Transact-SQL)

다음을 통해 공유