problem with row(s) affected

microsoft.public.sqlserver.programming

27 Sep 2006 9:00 PM

SQL Learner

SQL Server 2000.
Inside a stored procedure I wrapped and update in 2 print statements as
follows:

PRINT 'e'
UPDATE dbo.MailNotificationRecipients SET SendTo = @SendTo, CopyTo =
@CopyTo
    WHERE UserGroupID = @UserGroupID
PRINT 'f'

The output is suprising to me

e

(1 row(s) affected)

(2 row(s) affected)

f

The table is pretty much straight forward

CREATE TABLE [MailNotificationRecipients] (
    [UserGroupID] [int] NOT NULL ,
    [SendTo] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
    [CopyTo] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
    CONSTRAINT [PK_MailNotificationRecipients] PRIMARY KEY CLUSTERED
    (
        [UserGroupID]
    ) ON [PRIMARY]
)

There is exactly one row in MailNotificationRecipients.
Also I have verified that there are no triggers on the table. Where
does (2 row(s) affected) come from?

I deliberately changed the code introducing a bug as follows

PRINT 'e'
UPDATE dbo.MailNotificationRecipients SET SendTo = @SendTo, CopyTo =
@CopyTo
---- True for all the rows in the table.
    WHERE UserGroupID = UserGroupID
PRINT 'f'

And the result is even more surprising

e

(1 row(s) affected)

(4 row(s) affected)

f

Where does (4 row(s) affected) come from?