ExecuteScalar always returns null when calling a scalar-valued function

Question

Why does this return null?

//seedDate is set to DateTime.Now; con is initialized and open. Not a problem with that
using (SqlCommand command = new SqlCommand("fn_last_business_date", con))
{
       command.CommandType = CommandType.StoredProcedure;
       command.Parameters.AddWithValue("@seed_date", seedDate);//@seed_date is the param name
       object res = command.ExecuteScalar(); //res is always null 
}

But when I call this directly in the DB as follows:

select dbo.fn_last_business_date('8/3/2011 3:01:21 PM') 
returns '2011-08-03 15:01:21.000'

which is the result I expect to see when I call it from code

Why, why, why?

score 28 · Answer 1 · answered Aug 03 '11 at 19:56

28

Why does everyone insist on the select syntax?..

using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("calendar.CropTime", c))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.DateTime).Direction = ParameterDirection.ReturnValue;
    cmd.Parameters.AddWithValue("@d", DateTime.Now);

    cmd.ExecuteNonQuery();

    textBox1.Text = cmd.Parameters["@RETURN_VALUE"].Value.ToString();

}

answered Aug 03 '11 at 19:56

GSerg

76,472
17
159
346

6

I wonder if the downvoter who didn't leave a comment actually proved the above wrong, or just thought Hey, that's not how I usually do it. – GSerg Aug 03 '11 at 20:03
Probable the first one, I agree with your answer :D, it is a funciton, it should work , i guess he made a downvote because it is not related to the post, for the textbox and the calendar.CropTime a nd the date parameter xD, maybe for that (+1) – Hector Sanchez Aug 03 '11 at 20:43
2

Just as comment, if I rememeber well, just to make it clear, when you use a function, the return value must be the first parameter of the parameter collection, if the function expects more params. – Hector Sanchez Aug 03 '11 at 20:45
1

+1 - this technique works. But note the following caveat - treating a UDF as though it was a stored proc in this way will only work if the UDF is a scalar valued UDF. If the UDF is a table valued one (either inline or multi-statement), then you will get an error like : "The request for procedure 'Name_Of_UDF' failed because 'Name_Of_UDF' is a table valued function object." – Moe Sisko Aug 04 '11 at 00:16
2

@Moe Oh yes. Calling a *scalar* UDF is the very point of the OP's question. – GSerg Aug 04 '11 at 07:21

jenson-button-event · Accepted Answer · 2011-08-03T19:41:57.723

8

try:

using (SqlCommand command = new SqlCommand("select dbo.fn_last_business_date(@seed_date)", con))
{
       command.CommandType = CommandType.Text;
       command.Parameters.AddWithValue("@seed_date", seedDate);//@seed_date is the param name
       object res = command.ExecuteScalar(); //res is always null 
}

edited Aug 03 '11 at 19:41

answered Aug 03 '11 at 19:36

jenson-button-event

18,101
11
89
155

I don't have a problem returning the value from the sql function. It does what it should. My problem is on the c# code. I don't understand why the result is always null if I am passing in a valid @seed_date; If it helps, I can paste the sql_function code. The only thing special about my sql_function is that it uses recursion... may that me a problem? That would be weird. – Icarus Aug 03 '11 at 19:38
is fn_last_business_date actually a stored proc? if not make sure you add the select and make the command type Text (see my edits) – jenson-button-event Aug 03 '11 at 19:40
hehehe, I just replied to gbn that I had thought about doing that but I thought it wasn't elegant ;) I will try it and if it works I will give you due credit. Thanks. – Icarus Aug 03 '11 at 19:44
Credit for what? Reading links in my answer and then changing raw SQL to some .net? See the "edited: xx mins ago" link... – gbn Aug 03 '11 at 19:47
Okay, I ended up doing this: SqlCommand command = new SqlCommand("select dbo.fn_last_business_date('"+seedDate.ToShortDateString()+"')", con) and it worked. I'll have to leave with this. Upvoted your suggestion. Thanks! – Icarus Aug 03 '11 at 19:48
@gbn don't flatter yourself. i saw no links. – jenson-button-event Aug 03 '11 at 19:50

score 1 · Answer 3 · edited May 23 '17 at 12:19

1

You are actually getting an error that isn't being caught. You don't call scalar udfs like you call stored procedures.

Either wrap the udf in a stored proc, or change syntax. I'm not actually sure what that is because it isn't common...

Ah ha: see these questions:

edited May 23 '17 at 12:19

Community

1
1

answered Aug 03 '11 at 19:38

gbn

422,506
82
585
676

Thank you, do you have an example on how to call functions then? I thought of creating a statement like so: new SqlCommand("select dbo.fn_last_business_date("+seedDate.ToString()+") and then do command.CommandType=CommandType.Text but that didn't looked "elegant" :P – Icarus Aug 03 '11 at 19:42
@gbn: thanks for the links. They were both helpful. I had no idea that this was the "right" way of calling sql-functions in .NET. Upvoted your answer as well. – Icarus Aug 03 '11 at 19:56

ExecuteScalar always returns null when calling a scalar-valued function

3 Answers3

Linked