getting number of records updated or inserted in sql server stored procedure

Question

I have an SP that inserts some records and updates others and deletes some. What I want is to return the count values of what was inserted and what was updated and what was deleted. I thought I could use @@ROWCOUNT but that is always giving me a 1.

After my INSERT I run:

PRINT @@ROWCOUNT

But my message console shows what really happened and this number:

(36 row(s) affected)
1

So I can see that 36 records were actually updated but @@ROWCOUNT returned a 1.

I am trying to do the same thing after the UPDATE and DELETE parts of the SP runs with the same result.

Solution 1

@@ROWCOUNT will show the number of rows affected by the most recent statement - if you have any statements between the INSERT and the PRINT then it will give you the wrong number.

Can you show us a little more code so we can see the order of execution?

Solution 2

Depending on how @ninesided's answer works for you, you could also use the output clause on each update/insert/delete and get the counts from there.

Example:

declare @count table
(
    id int
)

update mytable
set oldVal = newVal
output inserted.field1 into @count

select count(*) from @count

You could reuse the count table throughout, and set variables as needed to hold the values.