Calling SQL Defined function in C#

Question

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;
}

Are you having a problem? Would you like to tell us what it is? — SLaks
– SLaks, Commented Jun 11, 2013 at 14:49
Possible duplicate of ExecuteScalar always returns null when calling a scalar-valued function — GSerg
– GSerg, Commented Aug 23, 2018 at 12:45

Massimiliano Kraus · Accepted Answer · 2018-03-16 09:27:06Z

58

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;
}

edited Mar 16, 2018 at 9:27

Massimiliano Kraus

3,8925 gold badges32 silver badges51 bronze badges

answered Jun 11, 2013 at 14:49

Curtis

104k69 gold badges280 silver badges359 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Scott Chamberlain Over a year ago

Actually because he did Totalf.CommandType = CommandType.StoredProcedure he can just call the function name.

Curtis Over a year ago

@ScottChamberlain Are you sure? What if there was a stored procedure called Tcupom? Surely SQL Server would get confused...

Scott Chamberlain Over a year ago

No, you are right, I just realized he was using a scalar function, not a table based function. I don't know if those can be called via SqlCommand set as StoredProcedure

Sam Saarian Over a year ago

number of corrections: 1. your method has a void return type and, you are returning something else (return SAIDA;) 2. you have initialize an SqlDataAdapter and it is never used. 3. ExecuteScalar(); returns an object, needs type conversion.

MoniR Over a year ago

It needs many corrections and after all, its not working: SqlDataAdapter da2 = new SqlDataAdapter(); never used? SqlParameter code1 = new SqlParameter("@code", SqlDbType.Int); never used?

|

Yargicx · Accepted Answer · 2018-11-19 17:46:01Z

9

...

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();
}

...

answered Nov 19, 2018 at 17:46

Yargicx

1,7543 gold badges16 silver badges39 bronze badges

1 Comment

Bucket Over a year ago

This is exactly what i needed. The function name ExecuteScalar is frustratingly misleading when calling SQL Scalar Functions instead of SQL Store Procedures. But the behaviour does match the documentation

Jason Boydell · Accepted Answer · 2019-01-21 12:51:43Z

I wanted to call a SQL Function in the same way that I call Stored Procedures which is by using DeriveParameters and then setting the parameter values. It turns out that this works with Scalar Functions out of the box and you can use ExecuteNonQuery and read the RETURN_VALUE. Please see sample code below:

    public int GetLookupCodeFromShortCode(short tableType, string shortCode)
    {
        using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
        {
            conn.Open();

            using (var cmd = new SqlCommand("dbo.fnGetLookupCodeFromShortCode", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 30;
                SqlCommandBuilder.DeriveParameters(cmd);

                cmd.Parameters["@sintTableType"].Value = tableType;
                cmd.Parameters["@vchrShortCode"].Value = shortCode;
                cmd.Parameters["@chrLanguage"].Value = "en";
                cmd.Parameters["@chrCountry"].Value = "en";

                cmd.ExecuteNonQuery();

                return (int)cmd.Parameters["@RETURN_VALUE"].Value;
            }
        }

    }

The Scalar Function code looks like this:

CREATE FUNCTION [dbo].[fnGetLookupCodeFromShortCode]( @sintTableType SMALLINT, @vchrShortCode VARCHAR(5), @chrLanguage CHAR(2), @chrCountry CHAR(2))

RETURNS INT

AS

BEGIN

    DECLARE @intLookupCode  INT

    SELECT @intLookupCode = LV.intLookupCode 
    FROM    
    tblLookupValueDesc LVD
        INNER JOIN tblLookupValue LV ON LV.sintLookupTableType = LVD.sintLookupTableType AND LV.intTableKey = LVD.intTableKey
    WHERE   
    LVD.sintLookupTableType = @sintTableType
    AND LVD.vchrShortCode = @vchrShortCode
    AND LVD.chrCountry = @chrCountry
    AND LVD.chrLanguage = @chrLanguage

    RETURN @intLookupCode 

END

GO

Where is @RETURN_VALUE coming from? Is it generated by DeriveParameters? I've been running this as-is with the exception of the DeriveParameter call and keep getting "An SqlParameter with ParameterName '@RETURN_VALUE' is not contained by this SqlParameterCollection."

Collectives™ on Stack Overflow

Calling SQL Defined function in C#

3 Answers 3

9 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related