Recuperar valores en SQL Server procedimientos almacenados con ADO
En este artículo se muestra cómo recuperar valores en SQL Server procedimientos almacenados con ADO.
Versión del producto original: SQL Server
Número de KB original: 194792
Resumen
Hay problemas importantes que se deben tener en cuenta al intentar recuperar RAISERROR/PRINT/RETURN
valores de SQL Server procedimientos almacenados a través de Objetos de datos ActiveX (ADO). Estos son tres problemas:
RAISERROR
las instrucciones de SQL Server deben ser un nivel de gravedad de 11 a 18.Las instrucciones PRINT de SQL Server también pueden rellenar la colección de errores de ADO. Sin embargo, las instrucciones PRINT tienen el nivel de gravedad cero (0), por lo que se requiere al menos una
RAISERROR
instrucción en el procedimiento almacenado para recuperar una instrucción PRINT con ADO a través de la colección Errors.Los valores RETURN de un procedimiento almacenado deben estar asociados con al menos un conjunto de resultados.
Más información
En el ejemplo de código siguiente se muestra cómo examinar la colección Errores de ADO para obtener acceso a los RAISERROR/PRINT/RETURN
detalles de un procedimiento almacenado de SQL Server que devuelve varios conjuntos de resultados:
Pegue y ejecute el código siguiente en la ventana SQL Server Management Studio (SSMS) después de crear la base de datos Pubs para crear el procedimiento almacenado usado para el ejemplo de ADO en el paso 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
Cree un proyecto de .EXE estándar en Visual Basic. Form1 se crea de forma predeterminada.
En el menú Proyecto, elija Referencias y seleccione La biblioteca de objetos de datos de Microsoft ActiveX.
Nota:
Debe usar ADO versión 2.0 o posterior para que el código funcione correctamente. Puede obtener los componentes de Microsoft Data Access Components (MDAC) más recientes en la Web en: MDAC.
Coloque un botón Comando en el formulario y, a continuación, pegue el código siguiente en la sección Declaraciones generales del formulario. Es posible que tenga que cambiar la cadena de conexión de la base de datos para su entorno.
'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
Cambie el valor de los parámetros de dos a siete para modificar el número de
PRINT
instrucciones oRAISERROR
instrucciones generadas por el procedimiento almacenado y devueltas a través de ADO. Vuelva a ejecutar el ejemplo de código de Visual Basic y tenga en cuenta que lasRAISERROR
instrucciones yPRINT
se devuelven a través de la colección de errores de ADO. Cambie los valores para experimentar con diferentes combinaciones de instrucciones con distintos conjuntos dePRINT/RAISERROR
resultados. Consulte los procedimientos almacenados de SQL para obtener soluciones alternativas específicas para casos especiales.
Para recuperar un valor RETURN en ADO con un procedimiento almacenado, debe haber al menos un conjunto de resultados. Para solucionar este problema, cuando no se especifica ningún conjunto de resultados (en el código de ejemplo de ADO), el procedimiento almacenado ejecuta un SELECT NULL para devolver un conjunto de resultados NULL a ADO rellenando así el valor RETURN. Además, para solucionar el problema de no especificar instrucciones y RAISERROR
una combinación de PRINT
instrucciones, se generan instrucciones predeterminadas RAISERROR
con el fin de proporcionar un contexto para devolver la PRINT
instrucción a través de ADO. Debe codificar RAISERROR
instrucciones en el formato que se muestra en el procedimiento almacenado porque solo los niveles de gravedad de 11-18 devuelven a través de la colección de errores de ADO.
Referencias
Comentarios
https://aka.ms/ContentUserFeedback.
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea:Enviar y ver comentarios de