none
"SET NOCOUNT ON" and SqlCommand.ExecuteNonQuery and SQLDataAdapter.Update RRS feed

  • Question

  • I have heard few side effects of using "SET NOCOUNT ON" 

    a. SQLCommand.ExecuteNonQuery function returning wrong number of rows if the stored procedure has SET NOCOUNT ON.

    b. SQLDataAdapter.Update throwing concurrency exception if SET NOCOUNT ON is set in the stored procedure specififed in the InsertCommand/DeleteCommand/UpdateCommand

    http://msdn2.microsoft.com/en-us/library/33y2221y(VS.80).aspx

    Are there any other reasons not to use "SET NOCOUNT ON" on a stored procedure or trigger ?

    Thanks!


    • Edited by GKS001 Wednesday, June 20, 2012 4:30 AM
    Wednesday, June 20, 2012 4:30 AM

Answers

  • SET NOCOUNT ON supresses the DONE_IN_PROC messages returned by the TDS protocol.  The ExecuteNonQuery method returns the cummulative count of those row count messages so you need to avoid SET NOCOUNT ON if you need to know the number of rows affected by a particular command.  For example, if you execute a SQL UPDATE statement with ExecuteNonQuery and use the returned value to determine the number of rows changed, you need to specify SET NOCOUNT OFF (the default).  Alternatively, you could use an output parameter (gleaned from @@ROWCOUNT in the SQL script) to return the number of affected rows to the application.

    If you don't need to know the number of affected rows, I see no harm in specifying SET NOCOUNT ON.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Wednesday, June 20, 2012 5:12 AM