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, Jun 11 '13 at 14:49
Possible duplicate of [ExecuteScalar always returns null when calling a scalar-valued function](https://stackoverflow.com/questions/6932199/executescalar-always-returns-null-when-calling-a-scalar-valued-function) — GSerg, Aug 23 '18 at 12:45

score 56 · Accepted Answer · edited Mar 16 '18 at 09:27

56

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 '18 at 09:27

Massimiliano Kraus

3,638
5
27
47

answered Jun 11 '13 at 14:49

Curtis

101,612
66
270
352

Actually because he did `Totalf.CommandType = CommandType.StoredProcedure` he can just call the function name. – Scott Chamberlain Jun 11 '13 at 14:50
@ScottChamberlain Are you sure? What if there was a stored procedure called `Tcupom`? Surely SQL Server would get confused... – Curtis Jun 11 '13 at 14:52
2

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 – Scott Chamberlain Jun 11 '13 at 14:53
@ScottChamberlain Sorry you still haven't convinced me. This article refers to "Using Parameters with a SqlCommand and a Stored Procedure", it mentions nothing about User Defined Functions. – Curtis Jun 11 '13 at 14:56
@ScottChamberlain To be honest I still don't understand how this would work with a table based UDF. I'd be interested to see some documentation. – Curtis Jun 11 '13 at 14:57
1

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. – Sam Saarian Dec 05 '17 at 22:18
@ScottChamberlain You were correct [originally](https://stackoverflow.com/questions/17047057/calling-sql-defined-function-in-c-sharp#comment24645607_17047099). One can [call a scalar function with `CommandType.StoredProcedure` and without a wrapping `select`](https://stackoverflow.com/a/6932462/11683). – GSerg Aug 23 '18 at 12:46
1

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? – MoniR Nov 29 '18 at 08:10
@MuniR the OP's code contains the unused `SqlDataAdapter` and the unused `SqlParameter` which means it could be utilized later on, or the OP could've removed it. I trust that it was left to the OP to figure out while the answerer focused purely on the setup for calling a UDF. – Hazel へいぜる Oct 26 '20 at 20:25

score 7 · Answer 2 · answered Nov 19 '18 at 17:46

...

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

...

score 5 · Answer 3 · answered Jan 21 '19 at 12:51

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." — atjoedonahue, Mar 26 '20 at 19:15

Calling SQL Defined function in C#

3 Answers3

Linked

Related