I have a scalar function in a SQL Server database which I want to to call using ADO.NET but it always returns null

Question

I have a scalar function that returns one value in SQL Server. I'm trying to get that value by calling ExecuteScalar, but it always returns NULL and C# throws a NullReferenceException.

I created a function that checks room availability; if the room is available, it should return the roomId, otherwise 0.

I'm calling that function in C# using ADO.NET, but in this case, it throws an exception.

ADO.NET code:

using (SqlConnection con = new SqlConnection(connectionString)) { var command = con.CreateCommand(); command.CommandType = CommandType.StoredProcedure; command.CommandText = "RoomAvailability"; con.Open(); int returnValue = (int)command.ExecuteScalar(); // The error happens on this line Console.WriteLine(returnValue.ToString()); }

SQL Server scalar function:

ALTER FUNCTION RoomAvailability() RETURNS INT AS BEGIN DECLARE @availability INT IF EXISTS(SELECT TOP 1 Id FROM Romm_Details WHERE status = 'F') BEGIN SET @availability = (SELECT TOP 1 Id FROM Romm_Details WHERE status = 'F') END ELSE BEGIN SET @availability = 0 END RETURN @availability //RETURN NULL VALUE END

Expected result :

if room available return room Id
else return 0

Probably, you should accept an answer if its solves your problem just as a vote of thanks. stackoverflow.com/help/someone-answers — Bharathi
– Bharathi, Commented Jul 12, 2019 at 2:33

marc_s · Accepted Answer · 2019-07-14 08:45:12Z

You will need to change how you invoke the SQL Function from C#.

Remove command.CommandType = CommandType.StoredProcedure; - you are invoking a function and not a stored procedure

You cannot invoke a SQL function just by its name (like a stored procedure), you will need to use it as an inline query

 using (SqlConnection con = new SqlConnection(connectionString)) { var command = new SqlCommand("SELECT dbo.RoomAvailability()", con); con.Open(); int returnValue = (int)command.ExecuteScalar(); Console.WriteLine(returnValue.ToString()); }

Moe Sisko · Accepted Answer · 2019-07-24 06:26:22Z

For a scalar udf, it is possible to use CommandType.StoredProcedure, but you need to add a ReturnValue parameter to get the result. e.g.:

 var command = con.CreateCommand(); command.CommandType = CommandType.StoredProcedure; command.CommandText = "RoomAvailability"; con.Open(); SqlParameter p = new SqlParameter("@res", SqlDbType.Int); p.Direction = ParameterDirection.ReturnValue; command.Parameters.Add(p); command.ExecuteNonQuery(); int returnValue = (int)p.Value;

Or alternatively, use the technique using CommandType.Text (the default CommandType) as described in Bharathi's answer.

Collectives™ on Stack Overflow

I have a scalar function in a SQL Server database which I want to to call using ADO.NET but it always returns null

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related