Tukukkk

I have written this scalar function in TSQL:

create function TCupom (@cupom int)

returns float

as

begin

    declare @Tcu float;



    select @Tcu = sum (total) from alteraca2 where pedido = @cupom 



    if (@tcu is  null)

        set @tcu = 0;



    return @tcu;

end

I want to call this function in my C# code. Here's what I have so far:

public void TotalCupom(int cupom)

{ 

    float SAIDA;           

    SqlDataAdapter da2 = new SqlDataAdapter();



    if (conex1.State == ConnectionState.Closed)

    { 

        conex1.Open();

    }



    SqlCommand Totalf = new SqlCommand("Tcupom", conex1);

    SqlParameter code1 = new SqlParameter("@code", SqlDbType.Int);

    code1.Value = cupom ;

    Totalf.CommandType = CommandType.StoredProcedure ;

    SAIDA = Totalf.ExecuteScalar();



    return SAIDA;

}

You can't just call the function name, you will need to write an inline SQL statement which makes use of the UDF:

SqlCommand Totalf = new SqlCommand("SELECT dbo.Tcupom(@code)", conex1);

And remove the CommandType, this isn't a Stored Procedure, its a User Defined Function.

In all:

public void TotalCupom(int cupom)

{ 

    float SAIDA;           

    SqlDataAdapter da2 = new SqlDataAdapter();

    if (conex1.State == ConnectionState.Closed)

    {

        conex1.Open();

    }

    SqlCommand Totalf = new SqlCommand("SELECT dbo.Tcupom(@code)", conex1);

    SqlParameter code1 = new SqlParameter("@code", SqlDbType.Int);

    code1.Value = cupom;

    SAIDA = Totalf.ExecuteScalar();



    return SAIDA;

}

...



try

{

    if (connectionState != ConnectionState.Open)

        conn.Open();

    using (var cmd = conn.CreateCommand())

    {

        cmd.CommandText = "udfName";

        cmd.CommandType = CommandType.StoredProcedure;



        foreach (var cmdParam in sqlParams)

        {

            cmd.Parameters.Add(cmdParam);

        }





        var retValParam = new SqlParameter("RetVal", SqlDbType.Int)

        {

            //Set this property as return value

            Direction = ParameterDirection.ReturnValue 

        };



        cmd.Parameters.Add(retValParam);

        cmd.ExecuteScalar();



        retVal = retValParam.Value;

    }

}

finally

{

    if (connectionState == ConnectionState.Open)

        conn.Close();

}



...