[MS-SQL] 프로시저에서 여러 행 데이터를 반환시킬 수 있을까?

MSSQL을 사용하는 프로젝트에서 프로시저의 OUTPUT 매개변수로 여러 데이터, 여러 Row를 반환시킬 수 있는지 확인해야하는 일이 생겨 포스팅한다.

 

내가 알기로는 프로시저의 OUTPUT 매개변수로 테이블이나 커서를 반환시킬 수 있었는데, JDBC를 통해서 DAO와 Service단으로 값이 전달이 되는지가 중요했다.

 

우선 CURSOR를 OUTPUT 매개변수로 뱉어내는 프로시저를 작성해보았다.

CREATE PROCEDURE SP_RETURN_CURSOR_TEST
(
    @IN_VALUE        NVARCHAR,
    @OUT_CURSOR        CURSOR VARYING OUTPUT
)
AS
BEGIN
 
    SET @OUT_CURSOR = CURSOR
    FORWARD_ONLY STATIC FOR
        SELECT *
        FROM TEST_TABLE
        WHERE ID LIKE '%' + @IN_VALUE + '%';
    OPEN @OUT_CURSOR;
 
END

 

그리고 매퍼 파일(XML)도 작성했다.

<resultMap id="userResultMap" type="hashmap" />
 
<select id="EXECUTE_PROCEDURE01" parameterType="hashmap" statementType="CALLABLE">
{
    CALL SP_RETURN_CURSOR_TEST
    (
        '테스트'
    , #{OUT_CURSOR,    mode=OUT,    jdbcType=CURSOR,    javaType=java.sql.ResultSet, resultMap=userResultMap}
    )
}
</select>

 

근데 실행하면 오류가 난다...  invalid jdbc data type -10 라고.......

 

그래서 한참 인터넷을 뒤지다가 이런걸 발견했다.

JDBC 드라이버에서는 OUT 매개 변수로 CURSOR, SQLVARIANT, TABLE 및 TIMESTAMP와 같은 SQL Server 데이터 형식을 사용할 수 없습니다.

 

그렇다면 프로시저에서 CURSOR를 반환하든 TABLE을 반환하든 JDBC를 통해서는 무용지물이라는건데... 그래도 혹시 모르니 좀 더 찾아보기로 했다.

 

그리고 StackOverFlow에서 답을 찾았다. 아예 생각을 바꿔서 프로시저의 OUTPUT대신 프로시저 내부 질의 결과를 가져다가 쓰는거다.

CREATE PROCEDURE SP_RETURN_ROWS_TEST
(
    @IN_VALUE        NVARCHAR, 
    @OUT_CODE        NVARCHAR(100)   OUTPUT
)
AS
BEGIN
    
    SET @OUT_CODE = '1';
 
    SELECT *
      FROM TEST_TABLE
     WHERE ID LIKE '%' + @IN_VALUE + '%'
 
END;

 

이렇게 프로시저를 작성하고 매퍼 파일을 수정해준다.

<select id="EXECUTE_PROCEDURE01" parameterType="hashmap" statementType="CALLABLE" resultType="hashmap">
{
    CALL SP_RETURN_ROWS_TEST
    (
        '테스트'
    , #{OUT_CODE,    mode=OUT,    jdbcType=NVARCHAR,    javaType=string}
    )
}
</select>

 

그리고 DAO에서 sqlSession.selectList(매퍼명, 파라미터맵) 메서드를 이용해 질의를 하면, OUTPUT으로 넘겨주는 변수는 파라미터맵에 세팅되고, selectList 메서드의 반환값으로 프로시저 내부의 SELECT 문의 결과 리스트가 세팅된다!

 

정상적인 방법인지는 모르겠으나, 일단 값은 제대로 전달되기에 포스팅했다.

---

참고 : https://docs.microsoft.com/ko-kr/sql/connect/jdbc/using-a-stored-procedure-with-output-parameters?view=sql-server-2017

출력 매개 변수가 있는 저장 프로시저 사용 - SQL Server

참고 : https://stackoverflow.com/questions/16126746/sql-stored-procedure-output-return-multiple-rows

SQL Stored Procedure OUTPUT Return Multiple Rows