반환 상태가 있는 저장 프로시저 사용

저장 프로시저(Stored Procedure) 내에서 반환값(Return Value)은 저장 프로시져의 실행 결과를 프로시저를 호출한 프로그램에게 알려주기 위한 용도로 사용되어 진다.

 

예를 들어 사용자로부터 이름을 받아 UserTable에 추가(Insert)하는 프로시저 UsingExistsStoredProcedure가 있다. 

이 프로시저는 UserTable 내에 추가하고자 하는 값이 중복되는지 확인한 후, 중복값이 있을 경우 추가할 수 없으므로 처리 결과값으로 0을 그 반대의 경우는 추가 가능함으로 결과값 1을 되돌려 준다.

 

먼저 UserTable을 만들어 보자. 

 

create table UserTable (
    UserID int IDENTITY(1,1) not null,
    UserName nvarchar(100),
    constraint PK_UserTable_UserID Primary Key Clustered (UserID)
)

그리고 프로시저를 만들어 보겠다. 

Create Procedure UsingExistsstoredprocedure  
(  
      @UserName VARCHAR(100)  
)  
As
declare @ResultValue int  
begin tran

if Exists ( select * from UserTable where UserName = @UserName)
    begin 
        set @ResultValue = -5
    end 
else
    begin
        insert into UserTable(UserName) values(@UserName)
        set @ResultValue = @@ERROR
    end

if @ResultValue <> 0
    begin 
        rollback tran
    end
else 
    begin
        commit tran
    end
return @ResultValue

 

위에 declare @ResultValue를 통해 반환값 변수를 선언했다. 신규 레코드를 추가 전, if exists를 사용하여 UserTable 내에 중복된 데이터가 있는지 체크를 한다. 

 

체크 결과 중복값이 있다면 -5를 그렇이 않다면 default 값이 @ResultValue에 저장된다. 이때 default값은 0이 될 것이다. 

 

그럼 한번 테스트해보자. 우선 Tom이라는 이름으로 실행을 했다. 결과는 예상대로 0이다. 

 

declare @return_value int 
exec @return_value = UsingExistsstoredprocedure 'Tom'  
select @return_value;

이번도 Tom이라는 이름으로 테스트해보겠다. 결과는 -5이다. 

이제 UserTable을 조회해보자.

Tom이 중복으로 저장되지 않았다.