Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    34

    Unanswered: Transactions in Stored Procedures?

    Do I need Transaction statements if I only have one SQL statement in my SP. I mean, if my DELETE fails, then it fails, so there would be nothing to roll back because it wouldn't have happened, right??? Or, is there more to this transaction thing than I realise...

    Here is the SP I am using:

    CREATE PROCEDURE delete_client
    @client_id int
    AS
    SET NOCOUNT ON
    BEGIN TRANSACTION
    DELETE FROM clients WHERE client_id = @client_id
    IF @@ROWCOUNT = 0 OR @@ERROR != 0
    BEGIN
    RAISERROR('delete_client SP FAILED', 16, 1)
    ROLLBACK TRANSACTION
    RETURN
    END
    COMMIT TRANSACTION

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Transactions consume sources, if it is possible - do not use them.
    If you do not care to much about sources and want to use transaction in this case - just remember that one rollback will roll back all begin tran, not only last one.

  3. #3
    Join Date
    Sep 2003
    Location
    Portland, Oregon
    Posts
    11

    Re: Transactions in Stored Procedures?

    Originally posted by raydenl
    Do I need Transaction statements if I only have one SQL statement in my SP. I mean, if my DELETE fails, then it fails, so there would be nothing to roll back because it wouldn't have happened, right??? Or, is there more to this transaction thing than I realise...

    Here is the SP I am using:

    CREATE PROCEDURE delete_client
    @client_id int
    AS
    SET NOCOUNT ON
    BEGIN TRANSACTION
    DELETE FROM clients WHERE client_id = @client_id
    IF @@ROWCOUNT = 0 OR @@ERROR != 0
    BEGIN
    RAISERROR('delete_client SP FAILED', 16, 1)
    ROLLBACK TRANSACTION
    RETURN
    END
    COMMIT TRANSACTION
    You should be able to rewrite this without the BEGIN/COMMIT. It will behave exactly the same.

    You only need a BEGIN/COMMIT set when you have more than one action being performed that should be committed or rolled back as a set.

    You code then becomes
    Code:
    CREATE PROCEDURE delete_client
    	@client_id int
    AS
    	SET NOCOUNT ON
    
    	DELETE FROM clients
    	WHERE client_id = @client_id
    
    	IF @@ROWCOUNT = 0 OR @@ERROR != 0 
    		BEGIN
    			RAISERROR('delete_client SP FAILED', 16, 1)
    			RETURN
    		END
    If someone disagrees with this, please explain.

    Rob

Posting Permissions

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