Geek Answers Handbook

ExecuteScalar always returns null when calling a scalar-valued function

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 it 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 that I expect to see when I call it from code

Why why why?

+4
source share
3 answers

to 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 }
+7
source

Why does everyone insist on 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(); }
+22
source

In fact, you get an error that doesn’t get there. You do not call scalar udfs, as you call stored procedures.

Either wrap udf in a stored proc, or change the syntax. I'm not sure what it is because it is not often ...

Yeah: see the following questions:

+1
source

More articles: