GVillani82 GVillani82 - 2 months ago 10
SQL Question

Call SQL Function using ADO .NET

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?

Answer

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