1. RETURN 문
======================================================================================
- 쿼리, 저장 프로시저 또는 일괄 처리를 무조건 종료.
- RETURN문 다음에 오는 문은 실행 안됨.
- 반환값은 정수(int) 값, NULL값을 반환 할 수 없다.
- 저장 프로시저는 오류가 발생하지 않으면 0을 반환, 값이 0이 아니면 오류가 발생한 것.
ex)
DECLARE @return_status int;
EXECUTE @return_status = <procedure_name>;
PRINT @return_status;
2. @@ERROR
======================================================================================
- 마지막 Transact-SQL 문이 성공적으로 실행되면 0을 반환하고 오류가 발생하면 해당 오류 번호를 반환.
- 일괄 처리, 저장 프로시저 및 트리거에서 발생한 경고에 대해서는 발생하지 않는다.
- @@ERROR의 값은 각 Transact-SQL 문이 완료될 때마다 변경.
- @@ERROR와 @@ROWCOUNT은 IF문 혹은 기타 테스트 문을 한번 실행하면 0으로 초기화됨. 다음 문장에서 참조 할 수 없다. Local 변수로 저장해서 참조 해야 함.
- @@ERROR와 @@ROWCOUNT를 모두 참조하려면 동일한 문에서 이 두 함수를 참조해야 함.
- 다양한 오류 처리는 TRY…CATCH 이용해야 한다.
3. RAISERROR
======================================================================================
- 시스템 오류나 경고 메시지와 같은 형식을 사용하여 응용 프로그램에 메시지를 다시 반환하는 데 사용.
- TRY…CATCH 구문의 TRY 블록에서 심각도 11-19로 RAISERROR를 실행하면 관련 CATCH 블록으로 제어가 전달된다.
CATCH 블록을 호출하지 않고 RAISERROR를 사용하여 메시지를 반환하려면 심각도를 10 이하로 지정해야 된다.
데이터베이스 엔진에서 연결을 닫도록 하는 심각도 20 이상의 오류는 TRY...CATCH 블록에서 처리되지 않는다.
- sys.messages에 있는 사용자 정의 메시지의 msg_id를 RAISERROR에 사용하면 msg_id가 SQL Server 오류 번호나 원시 오류 코드로 반환.
msg_id 대신 msg_str을 RAISERROR에 사용하면 반환되는 SQL Server 오류 번호와 원시 오류 번호는 50000이다.
- state
0에서 255 사이의 정수. 값이 음수이거나 255보다 크면 오류가 발생.
여러 위치에서 동일한 사용자 정의 오류가 발생하는 경우 각 위치의 고유 상태 번호를 사용하면 코드의 어떤 부분에서 오류가 발생하는지 찾는 데 도움이 된다.
- RAISERROR ( { msg_id | msg_str | @local_variable }
{ , severity, state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
- msg_id 조회
USE [MASTER]
GO
SELECT * FROM SYS.MESSAGES
4. TRY…CATCH
======================================================================================
1. 사용자 정의 함수에는 TRY…CATCH 구문을 사용할 수 없다.
ex)
BEGIN TRY
DECLARE @ErrorMsg NVARCHAR(50);
DECLARE @ErrorDB_ID INT;
DECLARE @ErrorDB_NAME NVARCHAR(128);
SELECT @ErrorMsg = N'Error raised in TRY block. : <<%07.3d>>, <<%10s>>'
, @ErrorDB_ID = DB_ID()
, @ErrorDB_NAME = DB_NAME();
-- RAISERROR with severity 11-19 will cause execution to
-- jump to the CATCH block.
RAISERROR ( @ErrorMsg -- Message text.
, 16 -- Severity.
, 1 -- State.
, @ErrorDB_ID
, @ErrorDB_NAME);
-- Severity가 11이상이라면 실행되지 않는다.
PRINT N'1. @@ERROR = ' + CAST(@@ERROR AS NVARCHAR);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE()
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
PRINT N'2. @@ERROR = ' + CAST(@@ERROR AS NVARCHAR) + ', ERROR_NUMBER() = ' + CAST(ERROR_NUMBER() AS NVARCHAR);
RAISERROR ( @ErrorMessage -- Message text.
, @ErrorSeverity -- Severity.
, @ErrorState -- State.
);
END CATCH;
5. 오류처리 전용 프로시저를 이용방법
======================================================================================
- 오류처리 프로시저에서 Transaction 처리하지 말고, 오류처리 프로시저를 호출하는 바깥쪽 CATCH 문에서 Rollback Transaction 처리를 해줘야 된다.
DROP PROCEDURE SP_GETERRORINFO
GO
-- 오류처리 프로시저
CREATE PROCEDURE SP_GETERRORINFO
AS
BEGIN
DECLARE @ErrorLine INT;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorNumber INT;
DECLARE @ErrorProcedure NVARCHAR(128);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorLine = ERROR_LINE()
, @ErrorMessage = ERROR_MESSAGE()
, @ErrorNumber = ERROR_NUMBER()
, @ErrorProcedure = ERROR_PROCEDURE()
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE();
RAISERROR ( N'ErrorLine : %d, ErrorMessage : %s, ErrorNumber : %d, ErrorProcedure : %s'
, @ErrorSeverity
, @ErrorState
, @ErrorLine
, @ErrorMessage
, @ErrorNumber
, @ErrorProcedure);
END;
-- 실행권한 부여
GRANT EXEC
ON SP_GETERRORINFO
TO PUBLIC
GO
SET XACT_ABORT ON -- Transact-SQL 문에서 런타임 오류가 발생할 경우 전체 트랜잭션이 종료된 후 롤백
SET XACT_ABORT OFF -- 일부 경우에 오류를 일으킨 Transact-SQL 문만 롤백되고 처리 작업을 계속진행. 기본 설정은 OFF
DROP PROCEDURE SP_TEST;
GO
-- 오류 발생 프로시저
CREATE PROCEDURE SP_TEST(@P_A INT, @P_B INT)
AS
BEGIN
DECLARE @RTN INT = 0;
BEGIN TRY
BEGIN TRANSACTION;
-- GENERATE DIVIDE-BY-ZERO ERROR.
SELECT @RTN = (@P_A / @P_B);
-- 출력 안된다.
PRINT N'1. OK';
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
EXECUTE SP_GETERRORINFO;
-- @@TRANCOUNT 혹은 XACT_STATE()로 Transaction Rollback 처리 한다.
-- XACT_STATE와 @@TRANCOUNT 함수는 모두 현재 요청에 활성 사용자 트랜잭션이 있는지 여부를 검색하는 데 사용될 수 있다.
-- @@TRANCOUNT를 사용하여 트랜잭션이 커밋되지 않은 트랜잭션으로 분류되었는지 여부를 확인할 수는 없다.
-- 또한 XACT_STATE를 사용하여 중첩된 트랜잭션이 있는지 여부를 확인할 수는 없다.
IF @@TRANCOUNT > 0
BEGIN
-- 당연 출력된다.
PRINT N'2. Transation Count : ' + CAST(@@TRANCOUNT AS NVARCHAR);
ROLLBACK TRANSACTION;
END
-- -1 : COMMIT 할수 없는 트랜잭션이 있는 상태
-- 0 : 활성 사용자 트랜잭션 없는 상태
-- 1 : COMMIT 할수 있는 트랜잭션이 있는 상태
IF XACT_STATE() <> 0
BEGIN
-- 당연 출력된다(IF @@TRANCOUNT > 0 처리가 없다면 여기서 처리된다).
PRINT N'3. Transation Count : ' + CAST(@@TRANCOUNT AS NVARCHAR);
ROLLBACK TRANSACTION;
END
END CATCH
-- 출력 된다.
PRINT N'4. DONE';
RETURN @RTN;
END;
GO
GRANT EXEC
ON SP_TEST
TO PUBLIC
GO
DECLARE @RTN INT;
EXECUTE @RTN = SP_TEST 1, 0;
PRINT @RTN;
GO
SELECT @@TRANCOUNT, XACT_STATE();