Recuperar valores em procedimentos armazenados SQL Server com a ADO
Este artigo mostra como recuperar valores em SQL Server Procedimentos Armazenados com ADO.
Versão original do produto: SQL Server
Número de KB original: 194792
Resumo
Há problemas importantes a serem considerados ao tentar recuperar RAISERROR/PRINT/RETURN
valores de SQL Server procedimentos armazenados por meio do ADO (ActiveX Data Objects). Aqui estão três problemas:
RAISERROR
as instruções em SQL Server devem ser um nível de gravidade de 11 a 18.As instruções PRINT no SQL Server também podem preencher a coleção de erros do ADO. No entanto, as instruções PRINT são nível de gravidade zero (0) portanto, pelo menos uma
RAISERROR
instrução é necessária no procedimento armazenado para recuperar uma instrução PRINT com ADO por meio da coleção Errors.Os valores RETURN em um procedimento armazenado devem ser associados a pelo menos um conjunto de resultados.
Mais informações
O exemplo de código a seguir demonstra navegar pela coleção Erros do ADO para acessar os RAISERROR/PRINT/RETURN
detalhes de um procedimento armazenado SQL Server retornando vários conjuntos de resultados:
Cole e execute o seguinte código na janela SSMS (SQL Server Management Studio) depois de criar o banco de dados pubs para criar o procedimento armazenado usado para o exemplo do ADO na etapa 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
Crie um projeto standard .EXE no Visual Basic. O Form1 é criado por padrão.
No menu Projeto, escolha Referências e selecione a Biblioteca de Objetos de Dados do Microsoft ActiveX.
Observação
Você deve usar a versão 2.0 ou posterior do ADO para que o código funcione corretamente. Você pode obter os componentes mais recentes do MDAC (Microsoft Data Access Components) na Web em: MDAC.
Coloque um botão Comando no Formulário e cole o código a seguir na seção Declarações Gerais do Formulário. Talvez seja necessário alterar a cadeia de conexão do banco de dados para seu ambiente.
'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
Altere o valor dos parâmetros de dois a sete para alterar o número de
PRINT
instruções e/ouRAISERROR
instruções geradas pelo procedimento armazenado e retornadas por meio do ADO. Execute o exemplo de código do Visual Basic novamente e observe que asRAISERROR
instruções ePRINT
são retornadas por meio da coleção de erros do ADO. Altere os valores para experimentar diferentes combinações de instruções com diferentes conjuntos dePRINT/RAISERROR
resultados. Consulte os procedimentos armazenados do SQL para obter soluções alternativas específicas para casos especiais.
Para recuperar um valor RETURN no ADO com um procedimento armazenado, deve haver pelo menos um conjunto de resultados. Para contornar esse problema, quando nenhum conjunto de resultados é especificado (no código de exemplo do ADO), o procedimento armazenado executa um SELECT NULL para retornar um conjunto de resultados nulo ao ADO, preenchendo assim o valor RETURN. Além disso, para contornar o problema de especificar nenhuma RAISERROR
instrução e uma combinação de instruções, instruções padrão RAISERROR
são geradas para fornecer um contexto para retornar a instrução por meio do PRINT
PRINT
ADO. Você deve codificar RAISERROR
instruções no formato mostrado no procedimento armazenado porque apenas os níveis de gravidade de 11 a 18 retornam por meio da coleção de erros do ADO.
Referências
Comentários
https://aka.ms/ContentUserFeedback.
Brevemente: Ao longo de 2024, vamos descontinuar progressivamente o GitHub Issues como mecanismo de feedback para conteúdos e substituí-lo por um novo sistema de feedback. Para obter mais informações, veja:Submeter e ver comentários