Does ExecuteScalar return immediately after SELECT?

Question

Interesting behaviour has been noticed by me recently.

When having MS SQL stored-procedure ran using SqlCommand.ExecuteScalar(), my application seems to be completely unaware to any SQL Errors or PRINTs which appear after SELECT is done.

Most probable explanation is that flow control is given to C# immediately after any SELECT result appears, without waiting stored procedure to finish (though stored procedure continues execution silently underneath).

Obvious advantage is performance gain (no need to wait, since the result is already known), unfortunately C# app is unaware of any SQL exceptions that could happen after that point.

Could anyone confirm my explanation? Could this behaviour be altered?

Answer 1

The ExecuteNonQuery method will call "ExecuteReader" and immediately call "Close" on the returned reader object. ExecuteScalar will call "Read" once, pick out the first value (index 0) and then call "Close".

Since the DataReader is essentially nothing more than a specialized network stream, any information that is returned afther it's current location (when Close is called) will just never reach the actual client components, even though the server might have sent it. The implementation is as such to avoid returning a huge amount of data when none is required.

In your case, I see two solutions to this problem.

1. make sure that you use ExecuteReader instead, and read all the way through the result:

   using(var reader = command.ExecuteReader())
   {
       do 
       {
             while (reader.Read()) { /* whatever */ };
       } while (reader.NextResult());
   }
   

2. If you can control the server side, it will help to move the actual "send-to-client" select to the end of the procedure or batch in question. Like this:

   create proc Demo
   as
   declare @result int
   select top 1 @result = Id from MyTable where Name = 'testing'
   print 'selected result...'
   select @result Id  -- will send a column called "Id" with the previous value
   go