We have a SQL
function which was written years ago that has been causing sporadic issues (for years I think).
I was finally able to get my hands on a backup of a customers DB so that I could figure out what is going on.
The function in question pulls the latest "session" ID from the DB and returns it unless @@ROWCOUNT
is equal to 0
, in which case it returns an empty string
.
ALTER FUNCTION [dbo].[GetCurrentSessionId]()
RETURNS nvarchar(255)
AS
BEGIN
declare @v nvarchar(255)
select top 1 @v = SessionId
from RestoreSession
where
SessionState <> 3 and -- Complete
SessionState <> 7 -- CompletedWithWarnings
order by SessionId desc
if @@ROWCOUNT = 0
set @v = ''
return @v
END
Every once in a blue moon this function suddenly will start always returning an empty string
even when there is a session in the DB.
When I looked at the above function for the first time while debugging the issue I noticed the @@ROWCOUNT
and thought that it was odd.
I removed the @@ROWCOUNT
condition and retested, this time the session ID was correctly returned. I then re-added the @@ROWCOUNT
condition and retested and it once again returns an empty string
.
Further details which may or may not matter. This function is being called from a C#
application using the utilities in the System.Data.SqlClient
namespace.
The method which calls the above SQL
function looks like the following.
public string GetCurrentSessionID()
{
string res = string.Empty;
using (var connection = _persistence.CreateSqlConnection())
{
var sessionsQuery = "select [dbo].GetCurrentSessionId() as SID";
using (var sqlCommand = connection.CreateCommand(sessionsQuery))
{
using (var sessionReader = sqlCommand.ExecuteReader())
{
if (sessionReader.Read())
{
if (!sessionReader.IsDBNull("SID"))
{
res = sessionReader.GetString("SID");
}
}
}
}
}
return res;
}
In the above C#
method both IsDBNull
and GetString
are custom extension methods that look like the following:
public static bool IsDBNull(this IDataRecord reader, string name)
{
return reader.IsDBNull(reader.GetOrdinal(name));
}
public static string GetString(this IDataRecord reader, string name)
{
return reader.GetString(reader.GetOrdinal(name));
}
I also want to mention that when I execute the SQL
function from within SSMS without the removal of the @@ROWCOUNT
conditional statement the session ID is correctly returned.
This only seems to occur when calling the function from the C#
application.
I believe the fix is removing the @@ROWCOUNT
conditional statement as when I did that the C#
application was able to correctly pull the session ID.
I'm curious what the issue could be in this case? Why does the @@ROWCOUNT
seem to "not work" sporadically when the function is called from the C#
application.