Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: 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:

    Code:
    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

  2. #2
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    @@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
    Cheers!





    !Direction is More Important than Speed!

    http://talatnauman.blogspot.com/

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    thats spot on, thanks

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Keep in mind (voice of experience speaking) that ANY SELECT operation will change the value of the @@ROWCOUNT system variable.
    Code:
    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.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  5. #5
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    24
    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


    Quote Originally Posted by mattock
    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:

    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •