SQL custom functions in Enterprise Library

Question

Is there a way to execute SQL custom functions with Enterpise Library? I've tried Database.ExecuteScalar() but for some reason it returns null.

This is my function:

Database db = DatabaseFactory.CreateDatabase("ConnectionString");
DbCommand cmd = db.GetStoredProcCommand("FunctionName");
db.AddInParameter(cmd, "Value1", DbType.String, Param1Value);
db.AddInParameter(cmd, "Value2", DbType.String, Param2Value);
return Convert.ToBoolean(db.ExecuteScalar(cmd));

Here the db.ExecuteScalar(cmd) method returns null. This does not happen with Stored Procedures.

By the way, im using version 4.0

Thanks.

Answer 1

You have to create a select statement which selects the result from the function and execute that.

"SELECT * FROM FunctionName(@Value1, @Value2)"

Or you can wrap your function call in a procedure and call the procedure, I prefer this.

Answer 2

For scalar functions it would be

SELECT FuncName(@Param1)

whereas a Table-Valued Function would be

SELECT * FROM FuncName(@Param1)