MSSQL stored procedure Return 사용법 및 특징

MSSQL stored procedure에서는 Return 문을 이용하여 특정한 값을 정수로 반환할 수 있습니다.

Stored procedure Return 특징

• Return 문은 stored procedure에서 반환되면 프로시저를 호출한 프로그램은 반환 값을 확인하고 프로그램을 중단할지, 무시할지 판단합니다.
• Stored procedure에서 output 매개변수를 사용하지 않고 Return 문 사용에서 특정 상태 값을 반환할 수 있습니다.
• Stored procedure Return의 반환되는 값은 정수(int)만 가능합니다.
• Return에서 성공하면 보편적으로 0을 반환합니다.

Stored procedure Return 사용법

Stored procedure에서 return 값 반환 방법.

• Stored procedure Return을 받기 위해서는 declare 변수를 한 후 확인할 수 있습니다.

Stored procedure Return문 소스 코드 설명 입니다.

• Example 소스 코드입니다.

DROP TABLE IF EXISTS UserInfoForProcedureReturn;
CREATE TABLE UserInfoForProcedureReturn (
    UserId int,
    UserNm varchar(255) NOT NULL,
    UserAge  int    NULL,
    UserAddress varchar(500)  NULL
);
-- DATA INSERT
INSERT INTO dbo.UserInfoForProcedureReturn(UserId,UserNm) 
VALUES (272, N'Ken')
      ,(273, N'Brian')
      ,(274, N'Stephen')
      ,(275, N'Michael')
      ,(276, N'Linda');
GO 
------------------------------------------------------------------------------
-- return  반환하는 기본
 -- DROP PROCEDURE STORE_PROCEDURE_RETURN_EX1;
CREATE  PROCEDURE [DBO].[STORE_PROCEDURE_RETURN_EX1] (      
     @UserId  int
) AS
BEGIN
    SELECT *
      FROM UserInfoForProcedureReturn R1
     where R1.UserId = @UserId ;

    IF(@@ROWCOUNT > 0 )   	
       RETURN(0); 		
    ELSE 
       RETURN(-1);
END;

-- EXCUTE  
DECLARE @return_status int;  
EXEC @return_status =  [STORE_PROCEDURE_RETURN_EX1] @UserId = '173' ; 
SELECT 'Return Status' = @return_status;  

-- EXCUTE   
DECLARE @return_status int;  
EXEC @return_status =  [STORE_PROCEDURE_RETURN_EX1] @UserId = '273' ; 
SELECT 'Return Status' = @return_status;

Return 내부에 SELECT 문을 사용하여 값을 반환하는 방법.

• Return으로 반환되는 DATA가 정수 타입이면 가능합니다.
• 기본적으로 COUNT를 계산해서 반환해 보겠습니다.

-- return 문에 SELECT 절을 사용하여 결과를 반환하는 방법
CREATE PROCEDURE [DBO].[STORE_PROCEDURE_RETURN_EX3] (      
    @UserId  int
) AS
BEGIN
 
RETURN(SELECT isnull(count(*),0) cnt
         FROM UserInfoForProcedureReturn R1
         WHERE R1.UserId = @UserId
      );  
END;

-- EXCUTE  
DECLARE @return_status int;  
EXEC @return_status =  [STORE_PROCEDURE_RETURN_EX3] @UserId = '1' ; 
SELECT 'Return Status' = @return_status;

Return 할 때 string 반환하면 어떻게 될까요?

• Stored procedure의 Return 반환되는 값은 정수 타입만 가능하므로 에러가 발생합니다.
• Msg 245, Level 16, State 1, Procedure STORE_PROCEDURE_RETURN_EX2, Line 12 [Batch Start Line 96]
  varchar 값 'OK'을(를) 데이터 형식 int(으)로 변환하지 못했습니다.

 -- return 값에 문자열 있는경우.
CREATE  PROCEDURE [DBO].[STORE_PROCEDURE_RETURN_EX2] (      
	 @UserId  int
) AS
BEGIN
    SELECT *
      FROM UserInfoForProcedureReturn R1
	 WHERE R1.UserId = @UserId ;

    IF(@@ROWCOUNT > 0 )    
       RETURN('OK');    
    ELSE 
       RETURN('ERROR');
END;

-- EXCUTE  
DECLARE @return_status VARCHAR(200);  
EXEC @return_status =  [STORE_PROCEDURE_RETURN_EX2] @UserId = '273' ; 
SELECT 'Return Status' = @return_status;

데이터를 반환할 수 있는 다른 방식은?

• 데이터를 반환할 때는 주로 function을 사용합니다.
• 사용자 정의 function에는 스칼라 함수와 테이블 반환 함수가 있습니다.
• RETURN 문은 사용자 정의 function보다 비교적 간단한 저장 프로시저에서 임의의 상태 값을 반환할 때 사용합니다.