CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
LuckWallace
LuckWallace

Reputation: 133

How to Get Error message from stored procedure into display on c#?

I have a stored procedure in my database that is the following:

ALTER PROCEDURE [dbo].[Cambiar_TerceroPedido_copy]
    @CodEmp as varchar(2) = '3',
    @TipoReg as varchar(2) = 'CF',
    @Pedido as integer,
    @NuevoCliente as varchar(10),
    @NuevoClienteEntrega as varchar(10) = null,
    @NuevoClienteFactura as varchar(10) = null,
    @NuevoClientePagoCobro as varchar(10) = null,
    @autorizador as varchar(10) = null,
    @error varchar(100) OUTPUT
AS
...

IF ((SELECT count(distinct T_FACTURAS.FACTURA)
        FROM T_FACTURAS INNER JOIN T_FACTURASL 
                ON T_FACTURAS.CODEMP = T_FACTURASL.CODEMP AND T_FACTURAS.TIPOREG = T_FACTURASL.TIPOREG AND T_FACTURAS.FACTURA = T_FACTURASL.FACTURA
        WHERE (T_FACTURAS.CODEMP = @CodEmp) 
                AND (T_FACTURAS.TIPOREG = @TipoReg) 
                AND (T_FACTURASL.NUMPED = @Pedido)
                AND (gg.dbo.getEsFechaControl(@CodEmp, 'MS', T_FACTURAS.FECHAF) = -1)
    ) > 0) BEGIN

    SET @error = 'ERROR!!!     El pedido tiene facturas asociadas. 1º debe eliminarlas' --+ cast (@CodEmp as varchar) + '/' + cast (@TipoReg as varchar) + '/' + cast (@Pedido as varchar) + '/'
    Print @error
    Return  
END

If I execute this code, I get an error in Microsoft SQL Server console, but I want to use this stored procedure in my C# code and I would like to know how I can get this error in a MessageBox?

Maybe store the error in a variable? And then adding parameters like this?

   strsql = "exec [dbo].[Cambiar_TerceroPedido_copy] '" + empresaGlobal.empresaID + "','" + c1 + "','" + c2numPed + "','" + c3provClient + "','" + c3provClient + "','" + c3provClient + "','" + c3provClient + "', 'JoseM'";
   SqlCommand cmd = new SqlCommand(strsql, conexion);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.Add("@error", SqlDbType.Char, 500);
   cmd.Parameters["@error"].Direction = ParameterDirection.Output;

Upvotes: 0

Views: 1583

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89006

how I can get this error in a MessageBox

Add an event handler for SqlConnection.InfoMessage.

The InfoMessage event occurs when a message with a severity of 10 or less is returned by SQL Server. Messages that have a severity between 11 and 20 raise an error and messages that have a severity over 20 causes the connection to close. For more information on SQL Server error levels, see Database Engine Error Severities.

Upvotes: 1

Related Questions