[MS-SQL] EXEC 프로시저 실행 후 프로시저 내에서 SELECT 결과값 리턴 받는 방법

프로시저내에서 프로시저를 실행하여 리턴값을 전달 받고 싶은 경우에 대한 처리 방법입니다.

1. 프로시저 실행 후 리턴값이 개별일경우 ( 1개 이거나 각각 리턴이거나)

첫번째 프로시져 생성합니다.

CREATE PROCEDURE dbo.SP_FIRST (
  @IDX  int
  ,@RTN_VAL1 varchar(10) OUTPUT
  ,@RTN_VAL2 int OUTPUT
)
AS
Begin

  SET @RTN_VAL1 = '집에가자'
  SET @RTN_VAL2 = 11
  RETURN
End
GO

 두번째 프로시저 생성 후 첫번째 프로시저를 호출하여 리턴값을 변수로 전달 받습니다.

CREATE PROCEDURE dbo.SP_SECOND (
  @SECOND_IDX  int
)
AS
Begin
 Declare @FIRST_VAL varchar(10)
 Declare @SECOND_VAL int

 EXEC SP_FIRST @SECOND_IDX , @FIRST_VAL output, @SECOND_VAL output

 SELECT @FIRST_VAL , @SECOND_VAL 

End
GO

두번째 프로시저를 실행합니다. 

EXEC SP_SECOND 1

첫번째 프로시저에서 리턴값이 개별로 2개임으로 반드시 2개의 리턴값을 받아야해요

그렇지 않으면 다음과 같은 오류가 발생합니다.

“메시지 201, 수준 16, 상태 4, 프로시저 SP_FIRST, 줄 26
프로시저 또는 함수 ‘SP_FIRST’에 매개 변수 ‘@RTN_VAL2’이(가) 필요하지만 제공되지 않았습니다.”

다음은 실행결과 스크린샷입니다.

리턴값을 SELECT문 대신 PRINT 문으로 찍어보셔도 됩니다.

2. 프로시저 실행 후 리턴값이 1개이상인 SELECT 문의 결과 인 경우 

임시테이블을 생성하거나 테이블변수를 생성하여 프로시저의 결과값을 리턴 받으면 됩니다.

--임시테이블에 리턴받는 방법
CREATE TABLE #TResults (
    SEQ_ID int
    ,USER_NAME nvarchar(20)
    ,USER_AGE int
)

INSERT #TResults EXEC 프로시저명

SELECT * FROM #TResults
DROP TABLE #TResults

--테이블 변수에 리턴 받는 방법

DECLARE @TResults TABLE(
    SEQ_ID int
    ,USER_NAME nvarchar(20)
    ,USER_AGE int
)

INSERT @TResults EXEC 프로시저명

SELECT * FROM @TResults

만약 링크드디비로 연결된 DB의 프로시저를 호출하여 데이터를 전달받는 경우 아래와 같은 “SQLNCLI11 분산 트랜잭션” 오류가 발생할 수 있습니다.

이럴 경우, 연결된 db서버를 선택하고 > 마우스 우클릭 > 속성 > 서버옵션에서
RPC, RPC 내보내기를 모두 TRUE 로 변경하세요. 

자세한 정보는 아래 블로그를 보시면 될 듯 싶네요.

[TIP]

프로시저 실행 후 리턴 값이 여러개 인경우, 리스트 1개, 총count 수 이렇게 2개의 리턴값이 있는 경우에는 

위의 방법으로 해결할 수 없습니다.