SELECT COUNT(*) returns no data SQL Server 2012

Question

I have a query, that as far as I know has failed exactly one time. It's a simple select count(*) from one table, no joins. But at least this once, executing that query resulted in no data read from SqlDataReader. Not even null, just nothing. First call to Read returns false. No exception was raised.

Has any one ever heard of that before? Any scenarios you can think of that would cause it?

I'm not even sure what to ask for to look at beyond SQL server logs. It's not something we can duplicate. I am assuming I'll have to chalk it up to a fluke and move on if/until it becomes a chronic problem. Here's a similar query:

SELECT ISNULL(COUNT(*),0)
FROM dbo.TRANSACTIONS T
WHERE (@TransactionId IS NULL OR (T.TransactionId != @TransactionId))
AND T.ParentId = @ParentId
AND (T.TransactionStatus != 4)

Is there a class of error that will thrown an exception out of the query, but not raise it to the application when executing a command or reading from the resulting SqlDataReader?

UPDATE: Here's the code executing this.

using (SqlConnection conn = /*connection created and opened here*/)
{
 SqlCommand cmd = new SqlCommand("usp_GetChildTransactions");
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add("@transactionId", SqlDbType.BigInt).Value = transactionId> 0 ? transactionId : (object)DBNull.Value;
 cmd.Parameters.Add("@parentId", SqlDbType.Int).Value = parentId;
 using (SqlDataReader rdr = conn.ExecuteReader(cmd))
 {
 rdr.Read();
 itemCount = rdr.IsDBNull(0) ? 0 : rdr.GetInt32(0); //exception thrown here because there is no data
 }
}

Update 2: I was hoping to not do this, but maybe the exception handling is part of the problem. Here is the create procedure for this, and also another stored procedure being called in the exception handler. Sorry for the length:

CREATE PROCEDURE dbo.usp_GetChildTransactions (
 @transactionId bigint,
 @parentId int,
AS
SET NOCOUNT ON 
BEGIN TRY
 SELECT ISNULL(COUNT(*),0)
 FROM dbo.TRANSACTIONS T
 WHERE (@TransactionId IS NULL OR (T.TransactionId != @TransactionId))
 AND T.ParentId = @ParentId
 AND (T.TransactionStatus != 4)
END TRY
BEGIN CATCH 
 EXEC usp_RethrowException
END CATCH 
CREATE PROCEDURE dbo.usp_RethrowException
AS
SET NOCOUNT ON 
DECLARE @ErrorMessage NVARCHAR(4000),
 @ErrorNumber INT,
 @ErrorSeverity INT,
 @ErrorState INT,
 @ErrorLine INT,
 @ErrorProcedure NVARCHAR(200)
SELECT @ErrorNumber = ERROR_NUMBER(),
 @ErrorSeverity = ERROR_SEVERITY(),
 @ErrorState = ERROR_STATE(),
 @ErrorLine = ERROR_LINE(),
 @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
if (@ErrorState = 0) set @ErrorState = 1
SELECT @ErrorMessage = ERROR_MESSAGE()
IF (LEFT(@ErrorMessage, 15) = N'( Error Number=')
BEGIN
 RAISERROR 
 (@ErrorMessage,
 @ErrorSeverity,
 @ErrorState)
END
ELSE
BEGIN
 -- First time - Create the message with all error information in a standard string format
 SELECT @ErrorMessage = N'( Error Number=%d, Severity=%d, State=%d, Procedure=%s, Line=%d )' + CHAR(13) + @ErrorMessage
 RAISERROR 
 (@ErrorMessage,
 @ErrorSeverity,
 @ErrorState,
 @ErrorNumber,
 @ErrorSeverity, 
 @ErrorState,
 @ErrorProcedure,
 @ErrorLine)
END

P.S. I did edit this a little, changing names of the procedures, the name of the table only, and removed comments only.

• Hannah Vernon: Without seeing your code, it's pretty hard to tell if there are any errors that might not be caught.
• Hannah Vernon: One thing I can tell you for certain, SQL Server will never ever return no result set, if rows exist in the table that match the WHERE clause requirements..
• Martin Smith: This is a scalar aggregate - it should always return exactly one row even if the source table was empty. COUNT(*) can never be NULL by the way so the ISNULL is pointless. You say " Here's a similar query" does the actual query have any GROUP BY?
• Martin Smith: Also very unlikely but does your code make use of set parseonly on or noexec or similar?
• Dean: Yeah I didn't add that ISNULL, but it's in the real query so I put it here. i'm not sure why someone would have done that. It made me wonder if this has happened before, and this was someone's misguided attempt at fixing it..

Answer 1

Is there a class of error that will thrown an exception out of the query, but not raise it to the application when executing a command or reading from the resulting SqlDataReader?

If the query errs in the scope of T-SQL structured error handling, the empty or partial result set is returned to the client followed by the error message in the TDS protocol stream. The implications are that client code should not assume SqlDataReader always returns true and that all rows and result sets must be consumed before the exception is raised in the client code. Consider the following example:

USE tempdb;
GO
CREATE PROCEDURE dbo.usp_ExampleError
AS
SET NOCOUNT ON;
BEGIN TRY
 SELECT COUNT(*)
 FROM (VALUES('NotAnInteger')) AS Example(BadData)
 WHERE BadData = 0;
END TRY
BEGIN CATCH 
 THROW;
END CATCH 
GO
using (var connection = new SqlConnection(@"Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI"))
using (var command = new SqlCommand("dbo.usp_ExampleError", connection))
{
 command.CommandType = CommandType.StoredProcedure;
 connection.Open();
 using (var reader = command.ExecuteReader())
 {
 Console.WriteLine(reader.Read()); //returns false
 Console.WriteLine(reader.NextResult()); //raises the conversion error
 }
}

Note that ExecuteNonQuery implicitly reads and ignores all rows and result sets (which will raise an exception if the batch erred) but ExecuteScaler and ExecuteReader do not. Also, DataAdaper.Fill consumes all results with a DataSet target overload but not with the DataTable overload,

This is one reason I suggest one generally avoid handling errors in T-SQL unless it provides value and specify SET XACT_ABORT ON to avoid continuing after errors and leaving transactions open.