MSSQL SP 에서 NOCOUNT 옵션

발췌 : 네이버 카페 ( http://cafe.naver.com/xlsvba/7031 )

MSSQL 에서 SP 사용시 NOCOUNT 옵션

SET NOCOUNT [ON | OFF]



알아두믄 피가되구 살이 되는 설정옵션이다...^^

물론 수시간의 삽질을 동반하구 알아냇다면 뇌속 깊숙이 박히겠지만

이런 기회에 알아두어두 별거 아니므로 기억하는데 어려움이 없으리라 본다.





일단 MSDN을 기초로 설명을 해보자믄



[정의]

: Transact-SQL 문 또는 저장 프로시저의 영향을 받은 행 수를 나타내는 메시지가

결과 집합의 일부로 반환되지 않도록 합니다.





[옵션별 설명]

ON : 실행쿼리에 의해 영향을 받은 ROW의 개수가 반환되지 않음(클라이언트에게 DONE_IN_PROC 메시지를 보내지 안는다.)

실제 데이터를 많이 반환하지 않는 일부 문이 포함된 SP 또는 T-SQL 루프가 포함된 SP의 경우

ON으로 설정하면 네트워크 트래픽이 크게 줄기 때문에 성능 향상에 도움이 된다.

VB에서 SP호출시 해당 SP내 실행쿼리가 존재할 경우(임시 혹은 일반테이블 모두)

레코드셋의 오픈이 아니되는데 이때 살행 쿼리구문에 앞서 ON으로 설정하고

실행쿼리 종료후 OFF로 설정하면 이러한 문제를 극복할 수 있다.

단 @@ROWCOUNT 함수는 정상적으로 업데이트된다. http://cafe.naver.com/xlsvba/3539



OFF : 실행쿼리에 의해 영향을 받은 ROW의 개수가 반환된다.





[예제]



가령 아래와 같은 SP를 만들고 SSMS에서 테스트 해보도록 하자.

정상적인 쿼리라면 결과값도 무리없이 출력될것이다.







CREATE PROC p_test
(
@idt INT
)
AS
BEGIN
SELECT u_id, u_nm, u_job INTO #tmp_test FROM t_test WHERE u_work = '1'
IF @idt = 0
   SELECT * FROM #tmp_test
ELSE IF @idt = 1
   SELECT * FROM #tmp_test WHERE u_cnt > 0
ELSE
   SELECT * FROM #tmp_test WHERE u_cnt < 0
END

일단 정상출력이라고 가정하고
이번엔 이넘을 VB에서 호출해 보자.

'## SQL ADO OLEDB 연결정상이라치구
Call ConnectADO(0)

'## 레코드셋 인스턴스 생성두 정상이라치구...
If oRs Is Nothing Then
   Set oRs = CreateObject("ADODB.Recordset")
End If

Sql = "EXEC [DBO].[p_test] 1"
oCn.Execute Sql
oRs.Open Sql, oCn, 3, 1
If oRs.recordcount <> 0 Then '## ERROR발생--> 개체가닫혀있으면..... 어쩌구저쩌구...~~~
...

SP내에 실행쿼리가 없다면 즉 실행쿼리에 의해 영향을 받은 ROW의 개수가 반환된것이 없다면(DONE_IN_PROC MESSAGE 미발생)

잘 돌아가겠지만 글치 안타믄.... 즉 실행쿼리에 의해 영향을 받은 ROW의 개수가 반환된것이 존재한다믄(DONE_IN_PROC MESSAGE 발생)

위와 같은 에러가 발생한다는 것이다.



그럼 간단하게 해결해 보도록하자..

ODBC어쩌구 저쩌구 하지말구 머리 아프니깐 간단하게 살자...

당연한 얘기이지만 임시이건 일반테이블이건 마찬가지란것에 주의해야한다...

잘못된 정보로 인해서 삽질하지 말자..

SP를 고쳐보도록 하자...



IF OBJECT_ID('p_test') IS NOT NULL
  DROP PROC p_test
GO

CREATE PROC p_test
(
@idt INT
)
AS
BEGIN
SET NOCOUNT ON
SELECT u_id, u_nm, u_job INTO #tmp_test FROM t_test  WHERE u_work = '1'
IF @idt = 0
    SELECT * FROM #tmp_test
ELSE IF @idt = 1
   SELECT * FROM #tmp_test WHERE u_cnt > 0
ELSE
   SELECT * FROM #tmp_test WHERE u_cnt < 0

SET NOCOUNT OFF

END


정상적으로 실행되리라 본다....

개인적으루 먹구살기 바쁘다 보니 양질의 강좌를 올리지 못하는점 미안하게 생각하고 있다.

물론 SQL강좌가 그리 인기가 있지 않은것이 다행이긴 하지만,,,, Good Bye..!!