저장프로시져 @@ERROR함수 사용 예

CREATE PROC usp_error

 @userid nvarchar(10),

 @name nvarchar(10),

 @birthYear INT = 1900,

 @addr NCHAR(4) = '서울',

 @mobile1 NCHAR(3) = NULL,

 @mobile2 NCHAR(8) = NULL,

 @height smallInt = 180

AS

 DECLARE @err INT;

 INSERT INTO userTbl(userID,name,birthYear,addr,mobile1,mobile2,height)

   VALUES (@userid,@name,@birthYear,@addr,@mobile1,@mobile2,@height);

 SELECT @err = @@ERROR;

 IF @err != 0

 BEGIN

  PRINT  '###' + @name + '을(를) INSERT에실패했습니다. ###'

 END;

 RETURN @err; -- 오류번호를 돌려줌.

------------------------------------------------------------

TRY...CATCH문 이용으로 변경

==>

CREATE PROC usp_tryCatch

 @userid nvarchar(10),

 @name nvarchar(10),

 @birthYear INT = 1900,

 @addr NCHAR(4) = '서울',

 @mobile1 NCHAR(3) = NULL,

 @mobile2 NCHAR(8) = NULL,

 @height smallInt = 180

AS

 DECLARE @err INT;

 BEGIN TRY 

   INSERT INTO 

userTbl(userID,name,birthYear,addr,mobile1,mobile2,height)

   VALUES (@userid, @name, @birthYear, @addr, @mobile1, 

@mobile2, @height)

 END TRY

 

 BEGIN CATCH

  SELECT ERROR_NUMBER()

  SELECT ERROR_MESSAGE()

 END CATCH