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]: stackoverflow.com/questions/4145329/… — Niladri Biswas
– Niladri Biswas, Commented Sep 11, 2012 at 7:41
@John But I want just retunr scalar value! Why use stored procedure? Are you sure I can't call directly Function? — GVillani82
– GVillani82, Commented Sep 11, 2012 at 14:22
@Cuong I tried with your code, but does not return the correct value. — GVillani82
– GVillani82, Commented Sep 11, 2012 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
– GVillani82, Commented Sep 11, 2012 at 14:33
If you have the line RETURN @RETURN_VALUE in your SP/Command then my answer should be helpful. — Jodrell
– Jodrell, Commented Sep 11, 2012 at 16:19

marc_s · Accepted Answer · 2016-10-09 15:28:50Z

5

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

edited Oct 9, 2016 at 15:28

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 11, 2012 at 7:44

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Comments

Jeff · Accepted Answer · 2012-09-11 15:34:08Z

2

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

answered Sep 11, 2012 at 15:34

Jeff

9182 gold badges9 silver badges24 bronze badges

Comments

Jodrell · Accepted Answer · 2012-09-12 07:38:00Z

1

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.

edited Sep 12, 2012 at 7:38

answered Sep 11, 2012 at 15:46

Jodrell

35.9k6 gold badges94 silver badges131 bronze badges

2 Comments

GVillani82 Over a year ago

SqlCommand command = New SqlCommand(queryString, connection). In this row, what is queryString?

Jodrell Over a year ago

@Joseph82, I edited the answer to more closely match your OP.

Collectives™ on Stack Overflow

Call SQL Function using ADO .NET

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related