Call SQL Function using ADO .NET

Question

I want call function created in SQL Server, which receives two parameters and returns an integer. When I call stored procedure, I use the following code:

    sqlcmd.CommandType = CommandType.StoredProcedure
    sqlcmd.CommandText = "PROCEDURE_NAME"

    sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@param1", Utilities.NothingToDBNull(user)))
    sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@param2", Utilities.NothingToDBNull(password)))
    da = New SqlClient.SqlDataAdapter()
    da.SelectCommand = sqlcmd
    table = New DataTable()
    da.Fill(table)

In this case I have a table returned by the stored procedure. What changes if I want use a function that returns a scalar value instead of stored procedure?

Possibly your are looking for [How do I call a TSQL function from ado.net][1] [1]: http://stackoverflow.com/questions/4145329/how-do-i-call-a-tsql-function-from-ado-net — Niladri Biswas, Sep 11 '12 at 07:41
@John But I want just retunr scalar value! Why use stored procedure? Are you sure I can't call directly Function? — GVillani82, Sep 11 '12 at 14:22
@Cuong I tried with your code, but does not return the correct value. — GVillani82, Sep 11 '12 at 14:27
The error message says: ParameterDirection 'ReturnValue' specified for parameter '@RETURN_VALUE' not è supported. The paramaters with table value support onlyParameterDirection.Input. — GVillani82, Sep 11 '12 at 14:33
If you have the line `RETURN @RETURN_VALUE` in your SP/Command then my answer should be helpful. — Jodrell, Sep 11 '12 at 16:19

score 5 · Accepted Answer · edited Oct 09 '16 at 15:28

You can't call that function directly, only StoredProcedure, Text (query), and TableDirect are allowed. Since you are already exposed with stored procedure, why not create a procedure that has the function on it?

In your C# code, you can use the ExecuteScalar of your command object

sqlcmd.CommandType = CommandType.StoredProcedure
sqlcmd.CommandText = "PROCEDURE_NAME"
sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@param1", Utilities.NothingToDBNull(user)))
sqlcmd.Parameters.Add(New SqlClient.SqlParameter("@param2", Utilities.NothingToDBNull(password)))

Dim obj as Object = sqlcmd.ExecuteScalar() 
' obj hold now the value from the stored procedure.

Your stored procedure should look like this now,

CREATE PROCEDURE PROCEDURE_NAME
    @param1 VARCHAR(15),
    @param2 VARCHAR(15)
AS
BEGIN
    SELECT function_name(@param1, @param2)
    FROM...
    WHERE....
END

score 2 · Answer 2 · answered Sep 11 '12 at 15:34

If you want to return a single value, you could call the function using a SELECT query

sql server code

CREATE FUNCTION Test
(
  @p1 varchar(10),
  @p2 varchar(10)
)
RETURNS varchar(20)
AS
BEGIN
  RETURN @p1 + @p2
END

vb.net code

Using cnn As New SqlClient.SqlConnection("Your Connection String")
  Using cmd As New SqlClient.SqlCommand("SELECT dbo.Test(@p1,@p2)", cnn)
    cmd.Parameters.AddWithValue("@p1", "1")
    cmd.Parameters.AddWithValue("@p2", "2")

    Try
      cnn.Open()
      Console.WriteLine(cmd.ExecuteScalar.ToString)  //returns 12
    Catch ex As Exception
      Console.WriteLine(ex.Message)
    End Try
  End Using
End Using

Jodrell · Answer 3 · 2012-09-12T07:38:00.603

If you want to return the value from the stored procedure as a single row, single column result set use the SqlCommand.ExecuteScalar method.

My preferred method is to actually use the return value of the stored procedure, which has been written accordingly with the TSQL RETURN statement, and call SqlCommand.ExecuteNonQuery.

Examples are provided for both on MSDN but for your specific situation,

Dim returnValue As SomeValidType

Using connection = New SqlConnection(connectionString))
    SqlCommand command = New SqlCommand() With _
        {
            CommandType = CommandType.StoredProcedure, _
            CommandText = "PROCEDURE_NAME" _
        }

    command.Parameters.Add(New SqlParameter() With _
        {
            Name = "@RC", _
            DBType = SomeSQLType, _
            Direction = ParameterDirection.ReturnValue  _ // The important bit
        }
    command.AddWithValue("@param1", Utilities.NothingToDBNull(user))
    command.AddWithValue("@param2", Utilities.NothingToDBNull(password))

    command.Connection.Open()
    command.ExecuteNonQuery()

    returnValue = CType(command.Parameters["@RC"].Value, SomeValidType)
End Using

As an aside, you'll note that in .Net 4.5 there are handy asynchronous versions of these functions but I fear that is beyond the scope of the question.

SqlCommand command = New SqlCommand(queryString, connection). In this row, what is queryString? — GVillani82, Sep 12 '12 at 07:12
@Joseph82, I edited the answer to more closely match your OP. — Jodrell, Sep 12 '12 at 07:38

Call SQL Function using ADO .NET

3 Answers3

Linked