ADO를 사용하여 SQL Server 저장 프로시저에서 값 검색

아티클
03/20/2024

이 문서에서는 ADO를 사용하여 저장 프로시저를 SQL Server 값을 검색하는 방법을 보여 줍니다.

원래 제품 버전: SQL Server
원래 KB 번호: 194792

요약

ADO(ActiveX Data Objects)를 통해 SQL Server 저장 프로시저에서 값을 검색 RAISERROR/PRINT/RETURN 하려고 할 때 고려해야 할 중요한 문제가 있습니다. 다음은 세 가지 문제입니다.

RAISERRORSQL Server 문은 심각도 수준 11-18이어야 합니다.
SQL Server PRINT 문은 ADO 오류 컬렉션을 채울 수도 있습니다. 그러나 PRINT 문은 심각도 수준 0(0)이므로 Errors 컬렉션을 통해 ADO를 사용하여 PRINT 문을 검색하려면 저장 프로시저에 하나 RAISERROR 이상의 문이 필요합니다.
저장 프로시저의 RETURN 값은 하나 이상의 결과 집합과 연결되어야 합니다.

추가 정보

다음 코드 샘플에서는 여러 결과 집합을 반환하는 SQL Server 저장 프로시저에서 세부 정보를 액세스 RAISERROR/PRINT/RETURN 하기 위해 ADO Errors 컬렉션을 검색하는 방법을 보여 줍니다.

Pubs 데이터베이스를 만든 후 SSMS(SQL Server Management Studio) 창에 다음 코드를 붙여넣고 실행하여 4단계에서 ADO 샘플에 사용되는 저장 프로시저를 만듭니다.

use pubs
GO

if exists (select * from sysobjects where id = object_id('dbo.ADOTestRPE') and sysstat & 0xf = 4)
    drop procedure dbo.ADOTestRPE
GO

create procedure ADOTestRPE
 (
     @SetRtn INT=0 OUTPUT,
     @R1Num INT=1,
     @P1Num INT=1,
     @E1Num INT=1,
     @R2Num INT=2,
     @P2Num INT=2,
     @E2Num INT=2
 )
 AS
 DECLARE @iLoop INT
 DECLARE @PrintText VARCHAR(255)
 DECLARE @iErrNum INT

/* Check for no Resultsets - needed to get the RETURN value back */ 
 IF @R1Num + @R2Num = 0 SELECT NULL

/* Resultset 1 ******************************* */ 

IF @R1Num > 0
    BEGIN
        SET ROWCOUNT @R1Num
        SELECT 'Resultset 1' RsNum, Title
        FROM Pubs..Titles
        SET ROWCOUNT 0
    END

/* Must raise a default error context in which to return the PRINT */ 
 /* statement */ 
 /* (if none present) since PRINT statements are a severity level of */ 
 /*0. */ 
IF (@P1Num > 0) AND (@E1Num = 0) RAISERROR ("RAISERROR.PError1", 11, 2)

IF @P1Num > 0
    BEGIN
        SELECT @iLoop = 0
        WHILE @iLoop < @P1Num
        BEGIN
        SELECT @iLoop = @iLoop + 1
        SELECT @PrintText = 'PRINT.Resultset.1: Line ' +
        CONVERT(char(2), @iLoop)
        PRINT @PrintText
    END
END

IF @E1Num > 0
    BEGIN
        SELECT @iLoop = 0
        WHILE @iLoop < @E1Num
        BEGIN
        SELECT @iLoop = @iLoop + 1
        SELECT @iErrNum = @iLoop + 201000
        RAISERROR ("RAISERROR.Resultset.1", 11, 2)
    END
END

/* Resultset 2 ******************************* */ 

IF @R2Num > 0
    BEGIN
        SET ROWCOUNT @R2Num
        SELECT 'Resultset 2' RsNum, Title
        FROM Pubs..Titles
        SET ROWCOUNT 0
    END

/* Must raise a default error context in which to return the PRINT */ 
/* statement */ 
/* (if none present) since PRINT statements are a severity level of */ 
/* 0. */ 
IF (@P2Num > 0) AND (@E2Num = 0) RAISERROR ("RAISERROR.PError2",11, 2)

IF @P2Num > 0
 BEGIN
 SELECT @iLoop = 0
 WHILE @iLoop < @P2Num
 BEGIN
 SELECT @iLoop = @iLoop + 1
 SELECT @PrintText = 'PRINT.Resultset.2: Line ' +
 CONVERT(char(2), @iLoop)
 PRINT @PrintText
 END
 END

IF @E2Num > 0
    BEGIN
        SELECT @iLoop = 0
        WHILE @iLoop < @E2Num
        BEGIN
        SELECT @iLoop = @iLoop + 1

        SELECT @iErrNum = @iLoop + 202000
        RAISERROR ("RAISERROR.Resultset.2", 11, 2)
    END
END

/* Return & Output ************************************ */ 

select @SetRtn = -1
RETURN @SetRtn
GO

Visual Basic에서 표준 .EXE 프로젝트를 만듭니다. Form1은 기본적으로 만들어집니다.
프로젝트 메뉴에서 참조를 선택하고 Microsoft ActiveX 데이터 개체 라이브러리를 선택합니다.

참고

코드가 올바르게 작동하려면 ADO 버전 2.0 이상을 사용해야 합니다. MDAC에서 웹에서 최신 MDAC(Microsoft Data Access Components) 구성 요소를 가져올 수 있습니다.

폼에 명령 단추를 놓고 폼의 일반 선언 섹션에 다음 코드를 붙여넣습니다. 사용자 환경에 대한 데이터베이스 연결 문자열을 변경해야 할 수 있습니다.

'This Code demonstrates RAISERROR/PRINT/RETURN values with ADO and
'multiple resultsets.

Sub CreateParms()

Dim ADOCmd As New ADODB.Command
Dim ADOPrm As New ADODB.Parameter
Dim ADOCon As ADODB.Connection
Dim ADORs As ADODB.Recordset
Dim sParmName As String
Dim strConnect As String
Dim rStr As String

On Error GoTo ErrHandler

strConnect = "driver={SQL
Server};server=(local);uid=sa;pwd=;database=pubs"

Set ADOCon = New ADODB.Connection
With ADOCon
.Provider = "MSDASQL"
.CursorLocation = adUseServer 'Must use Server side cursor.
.ConnectionString = strConnect
.Open
End With

Set ADOCmd.ActiveConnection = ADOCon
With ADOCmd
.CommandType = adCmdStoredProc
.CommandText = "ADOTestRPE"
End With

'Parameter 0 is the stored procedure Return code.
sParmName = "Return"
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
adParamReturnValue, , 0)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = -1

'Parameter 1 is the setting for the stored procedure Output
' parameter.
sParmName = "Output"
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
adParamOutput)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = 999

'Parameter 2
sParmName = "R1Num" 'Number of rows to return in Resultset 1.
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
adParamInput)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = 1

'Parameter 3
sParmName = "P1Num" 'Number of PRINT statements in Resultset 1.
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
adParamInput)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = 0

'Parameter 4
sParmName = "E1Num" 'Number of RAISERROR statements in Resultset
'1.
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
adParamInput)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = 0

'Parameter 5
sParmName = "R2Num" 'Number of rows to return in Resultset 2.
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
adParamInput)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = 2

'Parameter 6
sParmName = "P2Num" 'Number of PRINT statements in Resultset 2.
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
adParamInput)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = 0

'Parameter 7
sParmName = "E2Num" 'Number of RAISERROR statements in Resultset
' 2.
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
adParamInput)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = 0

Set ADORs = ADOCmd.Execute

Do While (Not ADORs Is Nothing)
    If ADORs.State = adStateClosed Then Exit Do
    While Not ADORs.EOF
        For i = 0 To ADORs.Fields.Count - 1
        rStr = rStr & " : " & ADORs(i)
    Next i
    Debug.Print Mid(rStr, 3, Len(rStr))
    ADORs.MoveNext
    rStr = ""
    Wend
    Debug.Print "----------------------"
    Set ADORs = ADORs.NextRecordset
Loop

Debug.Print "Return: " & ADOCmd.Parameters("Return").Value
Debug.Print "Output: " & ADOCmd.Parameters("Output").Value

GoTo Shutdown

ErrHandler:
Call ErrHandler(ADOCon)
Resume Next

Shutdown:
Set ADOCmd = Nothing
Set ADOPrm = Nothing
Set ADORs = Nothing
Set ADOCon = Nothing

End Sub

Private Sub Command1_Click()

Call CreateParms

End Sub

Sub ErrHandler(objCon As Object)

Dim ADOErr As ADODB.Error
Dim strError As String

For Each ADOErr In objCon.Errors
    strError = "Error #" & ADOErr.Number & vbCrLf & ADOErr.Description _
    & vbCr & _
    " (Source: " & ADOErr.Source & ")" & vbCr & _
    " (SQL State: " & ADOErr.SQLState & ")" & vbCr & _
    " (NativeError: " & ADOErr.NativeError & ")" & vbCr
    If ADOErr.HelpFile = "" Then
        strError = strError & " No Help file available" & vbCr & vbCr
    Else
        strError = strError & " (HelpFile: " & ADOErr.HelpFile & ")" _
        & vbCr & _
        " (HelpContext: " & ADOErr.HelpContext & ")" & _
        vbCr & vbCr
    End If
    Debug.Print strError
Next

objCon.Errors.Clear

End Sub

저장 프로시저에서 생성되고 ADO를 통해 반환되는 문 및/또는 RAISERROR 문의 수를 PRINT 변경하려면 매개 변수 값을 2-7로 변경합니다. Visual Basic 코드 샘플을 다시 실행하고 및 RAISERRORPRINT 문은 ADO 오류 컬렉션을 통해 반환됩니다. 값을 변경하여 다양한 결과 집합이 있는 PRINT/RAISERROR 다양한 문 조합을 실험합니다. 특수한 경우의 특정 해결 방법은 SQL 저장 프로시저를 참조하세요.

저장 프로시저를 사용하여 ADO에서 RETURN 값을 검색하려면 하나 이상의 결과 집합이 있어야 합니다. 이 문제를 해결하기 위해 ADO 샘플 코드에서 결과 집합이 지정되지 않은 경우 저장 프로시저는 SELECT NULL을 실행하여 Null 결과 집합을 ADO로 반환하여 RETURN 값을 채웁니다. 또한 문 없음 RAISERROR 및 문 조합을 PRINT 지정하는 문제를 해결하기 위해 ADO를 통해 문을 반환 PRINT 하기 위한 컨텍스트를 제공하기 위해 기본 RAISERROR 문이 생성됩니다. 11-18의 심각도 수준만 ADO 오류 컬렉션을 통해 반환되므로 저장 프로시저에 표시된 형식으로 문을 코딩 RAISERROR 해야 합니다.

참조

RAISERROR(Transact-SQL)

ADO를 사용하여 SQL Server 저장 프로시저에서 값 검색

요약

추가 정보

참조

피드백

피드백

추가 리소스