Получение значений в SQL Server хранимых процедур с помощью ADO

Статья
03/20/2024

В этой статье показано, как получить значения в SQL Server хранимых процедур с помощью ADO.

Оригинальная версия продукта: SQL Server
Исходный номер базы знаний: 194792

Сводка

При попытке получить RAISERROR/PRINT/RETURN значения из SQL Server хранимых процедур с помощью объектов данных ActiveX (ADO) следует учитывать важные проблемы. Вот три проблемы:

RAISERRORоператоры в SQL Server должны иметь уровень серьезности от 11 до 18.
Инструкции PRINT в SQL Server также могут заполнять коллекцию ошибок ADO. Однако инструкции PRINT имеют нулевой уровень серьезности (0), поэтому в хранимой процедуре требуется по крайней мере один RAISERROR оператор, чтобы получить инструкцию PRINT с ADO через коллекцию Errors.
Значения RETURN в хранимой процедуре должны быть связаны по крайней мере с одним набором результатов.

Дополнительная информация

В следующем примере кода показано, как просмотреть коллекцию ошибок ADO для доступа к подробным RAISERROR/PRINT/RETURN сведениям из SQL Server хранимой процедуры, возвращающей несколько наборов результатов:

Вставьте и выполните следующий код в окне SQL Server Management Studio (SSMS) после создания базы данных Pubs, чтобы создать хранимую процедуру, используемую для примера ADO на шаге 4:

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

Создайте проект .EXE уровня "Стандартный" в Visual Basic. Форма Form1 создается по умолчанию.
В меню Проект выберите Ссылки и библиотеку объектов данных Microsoft ActiveX.

Примечание.

Для правильной работы кода необходимо использовать ADO версии 2.0 или более поздней. Последние компоненты Microsoft Data Access Components (MDAC) можно получить в Интернете по адресу MDAC.

Поместите кнопку Command в форму, а затем вставьте следующий код в раздел Общие объявления формы. Может потребоваться изменить строку подключения к базе данных для вашей среды.

'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

Измените значение параметров с двух до семи, чтобы изменить количество инструкций PRINT и (или RAISERROR ) инструкций, созданных хранимой процедурой и возвращаемых через ADO. Запустите пример кода Visual Basic еще раз и обратите внимание, что инструкции RAISERROR и PRINT возвращаются через коллекцию ошибок ADO. Измените значения, чтобы поэкспериментировать с различными PRINT/RAISERROR сочетаниями операторов с разными наборами результатов. Конкретные обходные решения для особых случаев см. в хранимых процедурах SQL.

Чтобы получить значение RETURN в ADO с помощью хранимой процедуры, должен быть по крайней мере один набор результатов. Чтобы обойти эту проблему, если наборы результатов не указаны (в примере кода ADO), хранимая процедура выполняет SELECT NULL, чтобы вернуть в ADO набор результатов NULL, тем самым заполняя значение RETURN. Кроме того, чтобы обойти проблему отсутствия операторов и сочетания RAISERROR инструкций PRINT , создаются инструкции по умолчанию RAISERROR , чтобы обеспечить контекст для возврата инструкции PRINT через ADO. Операторы необходимо кодировать RAISERROR в формате, показанном в хранимой процедуре, так как через коллекцию ошибок ADO возвращаются только уровни серьезности 11–18.

Ссылки

RAISERROR (Transact-SQL)

Получение значений в SQL Server хранимых процедур с помощью ADO

Сводка

Дополнительная информация

Ссылки

Обратная связь

Обратная связь

Дополнительные ресурсы