MS-SQL Stored procedures 에서 Select 와 Output Parameter 동시 사용시 주의점

오늘의 주제는 제목과 같이 "MS-SQL Stored procedures 에서 Select 와 Output Parameter 동시 사용시 주의점" 이다. 이 문제 때문에 하루 반나절을 삽질을 하고야 말았다. ㅡㅡ^
이미 대부분의 사람들이 알고 있는 내용이겠지만 나 같이 MS-SQL 을 많이 사용해 보지 못한 사람에게는 나름 유익한 정보가 될거 같아서 남긴다.

결론 부터 말하자면
"MS-SQL 서버는 Select(RecordSet) 와 OUTPUT Parameter 동시에 사용할 때 해당 Select(RecordSet) 를 모두 읽어 들여야만 OUTPUT Parameter 에 값이 채워진다."
 
무슨 말인지 잘 이해가 안되면 다음을 보도록 하자.
 참고 DBMS 마다 특성이 달라서 모두 같은 방식으로 동작하지는 않고  MS-SQL 의 경우는 그렇다는 것이다.

이번에 개발중인 것이 하나 있는데 MS-SQL 를 데이타 베이스로 사용한다. 그래서 DB쪽을 개발해 주신 분이 Stored procedures 를 개발해 주셨고 거기에 대한 명세를 주셨다.
그 SP 가 USP_TestProc 이 라고 하자. 근데 USP_TestProc 는 Output Parameter  도 사용하고 결과를 RecordSet 으로도 반환한다. 간단히 예를 들면 다음과 같다.

SQL 에서 쿼리로 보면 다음과 같이 사용한다.

declare @OUT SMALL INT

exec USP_TestProc @OUT OUTPUT

select @OUT

이와 같이 쿼리를 만들고 수행하면 USP_TestProc 는 @OUT 변수에 값을 채워주고 Select 한 것과 같이 RecodeSet 으로도 반환 한다.
예들들어 @OUT 값이 1 이면 "output1" 이라는 필드에 값이 나타나고 @OUT 값이 2 이면 "output2" 필드에 값이 표시 되게 되어있다.

그러면 우리는 @OUT 값에 따라 필요한 필드값을 가져와서 처리하면된다. 여기까지는 아무런 문제가 없었다. 이 부분을 구현할때 문제가 발생했다. ㅠㅠ

이번에 데이타 베이스에 접속 하기 위해 SQLAPI 를 사용했다. (유료 라이브러리다) 그래서 샘플소스도 SQLAPI 로 작성한다. (ADO 를 많이 사용하기는 하지만 소스는 그게 그거니까 머 ㅡㅡ)

먼저 소스를 보면서 얘기해 보자

[소스1]

원래 의도대로 OUTPUT Parameter 인  OUT 의 값을 가지고 어떤 필드를 사용할지 결정하려고 하였다.
그러나 ret 변수의 값은 항상 0 이 리턴되었다.
왜 그럴까 ? 그것은 OUT 변수에 값이 할당되기 전에 값을 가지고 왔기 때문이다.
그럼 어떻게 해야 OUT 변수에 값이 할당이 될까 ? 그것은 처음에 말했듯이 모든 RecordSet 을 읽어온 후에야 제대로 값이 할당이 된다. 
그러면 제대로 값을 가지고 오려면 어떻게 해야 하나 ?  다음 소스를 보자

[소스2]

빨간 박스안의 소스를 추가 해보자. 빨간 박스 안의 소스는 Roop 를 돌면서 모든 RecordSet 을 순회 하며 읽어온다. 그리고  OUT 변수의 값을 받으면 정상적으로 값이 받아진다.

그리고 보너스로 아래 파란박스의 소스도 수정해야 한다. 그냥 쓴다면 Except 가 발생할것이다. ㅡㅡ

% 참고로 ADO 의 Execute 메소드에서는 ADODB::adExecuteNoRecords 옵션을 인자로 설정할 수가 있다.
이 옵션을 사용하면 바로  Output Parameter 에 값을 받아올수 있지만 RecordSet 을 받을수는 없다 ㅡㅡ