Effect of `SET NOCOUNT ON` on `@@ROWCOUNT`

From the @@ROWCOUNT documentation:

Statements such as USE, SET , DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

Which means that if you get into the good habit of beginning all your stored procedure bodies with SET NOCOUNT ON;, you’ll be in for a nasty surprise when you code a trigger with short-curcuit logic.

For example, in this audit trigger, the INSERT statement will never execute:

CREATE TRIGGER dbo.Wibble_InsertAuditRow
ON dbo.Wibble
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  IF @@ROWCOUNT = 0
  BEGIN
    RETURN;
  END;

  INSERT INTO Audit.WibbleHistory (
    Action,
    WibbleCode,
    WobbleSpec,
    GibletCount
  )
  SELECT
    'INSERT',
    WibbleCode,
    WobbleSpec,
    GibletCount
  FROM INSERTED
  UNION ALL
  SELECT
    'DELETE',
    WibbleCode,
    WobbleSpec,
    GibletCount
  FROM DELETED
END;

(Let’s assume that Audit.WibbleHistory has extra columns with default values that audit when and by whom each change is made.)

Because SET NOCOUNT ON sets @@ROWCOUNT to zero, the do-nothing code path will always execute.

To avoid this, make sure to check @@ROWCOUNT before setting NOCOUNT.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s