counting number of deleted rows

Hi

I have a stored procedure which deletes a number of rows from a number of different tables. How to i count/return the number of deleted rows in each table?

Here is my stored procedure if it helps:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_delete_entry]
@.new_venue_id int
AS
BEGIN


DECLARE @.new_customer_id int
SET @.new_customer_id = (SELECT customer_id FROM VENUE WHERE venue_id = @.new_venue_id)

DELETE FROM FEATURED WHERE venue_id = @.new_venue_id
DELETE FROM FACILITIES WHERE venue_id = @.new_venue_id
DELETE FROM SIC WHERE venue_id = @.new_venue_id
DELETE FROM SUBSCRIPTION WHERE venue_id = @.new_venue_id
DELETE FROM ADMIN WHERE venue_id = @.new_venue_id
DELETE FROM VENUE WHERE venue_id = @.new_venue_id
DELETE FROM CUSTOMER WHERE customer_id = @.new_customer_id

END

thanks@.@.rowcount global variable holds the number of rows affected for the latest query.
something like this.

ALTER PROCEDURE [dbo].[usp_delete_entry]
@.new_venue_id int
AS
BEGIN
DECLARE @.count1 int, @.count2 int ,@.count3 int, @.count4 int, @.count5 int,
@.count6 int, @.count7 int

DECLARE @.new_customer_id int
SET @.new_customer_id = (SELECT customer_id FROM VENUE WHERE venue_id = @.new_venue_id)

DELETE FROM FEATURED WHERE venue_id = @.new_venue_id
SET @.count1=@.@.rowcount
DELETE FROM FACILITIES WHERE venue_id = @.new_venue_id
SET @.count2=@.@.rowcount
DELETE FROM SIC WHERE venue_id = @.new_venue_id
SET @.count3=@.@.rowcount
DELETE FROM SUBSCRIPTION WHERE venue_id = @.new_venue_id
SET @.count4=@.@.rowcount
DELETE FROM ADMIN WHERE venue_id = @.new_venue_id
SET @.count5=@.@.rowcount
DELETE FROM VENUE WHERE venue_id = @.new_venue_id
SET @.count6=@.@.rowcount
DELETE FROM CUSTOMER WHERE customer_id = @.new_customer_id
SET @.count7=@.@.rowcount

select @.count1, @.count2,@.count3,@.count4,@.count5,@.count6,@.count7

END|||thats spot on, thanks|||Keep in mind (voice of experience speaking) that ANY SELECT operation will change the value of the @.@.ROWCOUNT system variable.
DECLARE @.recCount int
declare @.RowCount int
Select @.recCount = count(*) from osid (nolock)
select @.recCount
Select @.RowCount = @.@.ROWCOUNT
select @.RowCount

Not necessarily an issue - but something to be aware of, since I have had my buttocks spanked (Ohhhhhh...) on a few occasions by test stub code inserted or other such foolishness.|||Hi,

You can use @.@.ROWCOUNT global variable after each delete and assiging to other local variables to return etc.

I think it is helpful.

Cheers
Riaz

Hi

I have a stored procedure which deletes a number of rows from a number of different tables. How to i count/return the number of deleted rows in each table?

Here is my stored procedure if it helps:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_delete_entry]
@.new_venue_id int
AS
BEGIN


DECLARE @.new_customer_id int
SET @.new_customer_id = (SELECT customer_id FROM VENUE WHERE venue_id = @.new_venue_id)

DELETE FROM FEATURED WHERE venue_id = @.new_venue_id
DELETE FROM FACILITIES WHERE venue_id = @.new_venue_id
DELETE FROM SIC WHERE venue_id = @.new_venue_id
DELETE FROM SUBSCRIPTION WHERE venue_id = @.new_venue_id
DELETE FROM ADMIN WHERE venue_id = @.new_venue_id
DELETE FROM VENUE WHERE venue_id = @.new_venue_id
DELETE FROM CUSTOMER WHERE customer_id = @.new_customer_id

END

thanks