Object cannot be cast from DBNull to other types in stored procedure with output parameter

Question

I want to use a stored procedure using SQL Server in a C# application that returns an output parameter but after trying many different ways I haven't realized how to do this.

This procedure should return a variable/parameter called "recargo" (surcharge) which value varies depending on the age, gender and marital status of a client. The procedure also has an input parameter which is the ID of the client

The app returns a DBNull exception which I already tried fixing with code, but it still doesn't return a value. It's returning a null always.

CREATE OR ALTER PROCEDURE CalcularRecargo
    @rut NVARCHAR(10),
    @recargo DECIMAL OUTPUT
AS
BEGIN
    DECLARE @fecha DATETIME, 
            @sexo TINYINT, 
            @ecivil TINYINT,
            @edad INT

    SELECT 
        @fecha = cl.FechaNacimiento, 
        @sexo = cl.IdSexo, 
        @ecivil= cl.IdEstadoCivil 
    FROM 
        Contrato c 
    JOIN 
        Cliente cl ON c.RutCliente = cl.RutCliente 
    WHERE 
        c.RutCliente = @rut

    --HERE I CALCULATE THE AGE, I DON'T KNOW IF THIS IS CORRECT
    BEGIN      
        SET @edad = (CONVERT(INT, CONVERT(CHAR(8), GETDATE(), 112)) - CONVERT(CHAR(8), @fecha, 112)) / 10000
    END

    --if the age is between some of these ranges, the surcharge (@recargo) should be increased.
    IF @edad >= 18 AND @edad <=25 
    BEGIN 
        SET @recargo = @recargo + 3.6 
    END
    ELSE IF @edad >=26 AND @edad <=45 
    BEGIN 
        SET @recargo = @recargo + 2.4 
    END
    ELSE 
    BEGIN 
        SET @recargo = @recargo + 6 
    END

    --same with gender.
    IF @sexo = 1 
    BEGIN 
        SET @recargo = @recargo + 2.4 
    END
    ELSE 
    BEGIN 
        SET @recargo = @recargo + 1.2 
    END

    --same with marital status
    IF @ecivil = 1 
    BEGIN 
        SET @recargo = @recargo + 4.8 
    END
    ELSE IF @ecivil = 2 
    BEGIN 
        SET @recargo = @recargo + 2.4 
    END
    ELSE 
    BEGIN 
        SET @recargo = @recargo + 3.6 
    END

    RETURN
END;

Here is the C# code of the method:

public static double  CalcularRecargo(string rut)
{
    double recargo = 0.0;
    SqlConnection conexion = new SqlConnection(ConSql.conexion);

    try
    {
        conexion.Open();

        SqlCommand cmd = new SqlCommand("CalcularRecargo", conexion);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter ParRut = new SqlParameter("@rut", SqlDbType.VarChar);
        ParRut.Value = rut;
        cmd.Parameters.Add(ParRut);

        SqlParameter ParRecargo = new SqlParameter("@recargo", SqlDbType.Decimal);
        //ParRecargo.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(ParRecargo).Direction=ParameterDirection.Output;

        cmd.ExecuteNonQuery();

        // IF I UNCOMMENT AND USE THIS CODE IT STILL RETURNS A NULL.
        var prerecargo = cmd.Parameters["@recargo"].Value;

        if (prerecargo != DBNull.Value)
           @recargo = Convert.ToDouble(prerecargo);

        // IF I UNCOMMENT AND USE THE CODE BELOW IT RETURNS THE DBNULL EXCEPTION
        // recargo = Convert.ToDouble(cmd.Parameters["@recargo"].Value);
    }
    catch(Exception error)
    {
        MessageBox.Show(error.Message);
    }
    finally
    {
         conexion.Close();
    }

    return recargo;
}

And Here is the other part of the C# Code where I implement it:

private void button1_Click(object sender, EventArgs e)
{
    double recargo = 0;
    double primaanual = 0;
    double primamensual = 0;

    if (ComboTitular.Text != "")
    {
        recargo = Contrato.CalcularRecargo(ComboTitular.Text);
    }

    if (recargo > 0 && ComboPlan.Text != "")
    {
        primaanual = Plan.planes.Find(i => i.Nombre == ComboPlan.Text).PrimaBase + recargo;
        primamensual = primaanual / 12;

        LblPrimaAnual.Text = primaanual.ToString();
        LblPrimaMensual.Text = primamensual.ToString();
    }
    else
    {
        MessageBox.Show("Seleccione un plan por favor");
    }

    try
    {
        Contrato con = new Contrato(numcontrato, feccreacion, fectermino, ComboTitular.Text, ComboPlan.Text, poliza, inivig, finvig, estavig, declarasalud, primaanual, primamensual, observacion);

        string resultado = con.AgregarContrato(con);

        MessageBox.Show(resultado);
    }
    catch (Exception error)
    {
        MessageBox.Show("Contract already exists");
    }
}

NOTE: I deleted a lot of extra code to make the question clearer

Thanks in advance

score 3 · Accepted Answer · edited Jul 09 '18 at 03:42

I think the issue is that @recargo is NULL because it is never assigned an initial value before the calculations.

Find below a quick repro and documentation link for more details:

All arithmetic operators (+, -, *, /, %), bitwise operators (~, &, |), and most functions return null if any of the operands or arguments is null, except for the property IsNull Find more details at https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/handling-null-values

DECLARE @recargo DECIMAL

-- @recargo is NULL
SELECT @recargo


SET @recargo = @recargo + 2.4 
SELECT @recargo --NULL

SET @recargo = 0 --INITIALIZING THE VARIABLE
SET @recargo = @recargo + 2.4

-- @recargo is 2
SELECT @recargo

Thanks! The problem was that I hadn't initialized @recargo. Fixed it writting "SET recargo = 0" (@ before) before the IF-ELSE statements. Good answer — Runsis, Jul 09 '18 at 03:32

Object cannot be cast from DBNull to other types in stored procedure with output parameter

1 Answers1