저장프로시져 @@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