ExecuteNonQuery Gotcha

Here’s something that made me roll my eyes this week. I’ve been trying to get the number of rows executed returned from SQL Server to an ASP.Net data layer. It promised to be straight forward but it had a cheeky little gotcha nestled in the middle of it. To take the simplest example for demo purposes (all error handling type stuff omitted)

The stored procedure

CREATE PROCEDURE usp_TestProc
      @PersonId int
AS
BEGIN
      Set Nocount On

      Update Person
      Set
     FirstName = 'Willy',
            Surname = 'Wonka'
      Where
            Id = @PersonId
END

The calling business layer

conn.Open();
cmdNonQuery = conn.CreateCommand();
cmdNonQuery.CommandType = CommandType.StoredProcedure;
cmdNonQuery.CommandText = “usp_TestProc”;
cmdNonQuery.Parameters.AddRange(sqlParameters);
numberOfRecords = cmdNonQuery.ExecuteNonQuery();

Logic (and the .Net API documentation) dictates that the ExecuteNonQuery method returns the number of rows. But it steadfastly returns -1 no matter what is occurring in the database.

So what is happening?? It turns out that the Nocount setting stops this information coming back. Removing the statement – or setting NoCount Off causes the ExecuteNonQuery method to behave as expected. It returns -1 if no rows affected otherwise the number of rows actually affected.

This would probably be too arcane to worry about except the SQL Server standard template for creating stored procedures includes the Set NoCount On statement. In other words – select ‘create new procedure from the SQL Server menu’ and you get this template

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
      -- Add the parameters for the stored procedure here
      <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
      <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Very lovely – except it contains the very code that disables the proc return values. Ouch.