Records Affected for Stored Procedure 1

[andrea96]

Is there a way to get the number of records affected for each statement in a stored procedure, not the total number of records affected by the stored procedure that I would get using RecordsAffected? For example, I have a stored procedure that performs a delete and then an insert, and I need the number of records deleted and the number of records inserted.

Thanks,
Andrea

 

[SqueakinSweep]

You will have to use @@ROWCOUNT in your Stored Procedure
Something like below should work, although it takes no consideration for any triggers which may fire and perform cascade deletes

DECLARE @iInserted INT
DECLARE @iDelete INT

DELETE FROM MyTable WHERE.....
SET @iDeleted = @@ROWCOUNT

INSERT INTO MyTable............
SET @iInserted = @@ROWCOUNT

select @iInserted as Inserted, @iDeleted as Deleted

This will also give you a table back with the counts required. Im sure you can grasp the concepts and amend as you need to.




Sweep
...if it works dont mess with it

 

[andrea96]

Thanks. I had decided to use output parameters with SELECT COUNT(*), but @@ROWCOUNT is a better solution.

 

[SqueakinSweep]

You're welcome...glad to be of some help.

Sweep
...if it works dont mess with it