Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    South Africa
    Posts
    8

    Unanswered: Stored Procedure Double Insert

    Im just begining to learn MS SQL stuff, so please bare with me...
    Please let me know the best way of doing this:

    I want to register a user with a stored procedure:
    I first want to insert a record into table tbl_user with an automatic id.
    Then, if that goes well, insert into the second table tbl_user_details, with the same automaticly generated id.

    If there was a problem with the second table insert, rollback the changes of the first insert and raise an error.

    I currently have something like this:
    Code:
    CREATE PROCEDURE register
    		@email varchar(50),
    		@password varchar(15),
    		@activation_code varchar(10)
    AS
    
    SET NOCOUNT ON
    declare @id int
    insert into tbl_user(...) values(@email, @password, ...)
    SET @id = @@identity
    
    IF @@ERROR <> 0 
      begin
        print "Not Registered"
        return(99)
      end
    else
      insert into tbl_user_details(usr_id, ...) values(@id, ...)
      IF @@ERROR <> 0 
        // Rollback the changes of the first insert
      else
        print "Registration Complete"
        return(0)
      end if
    end if
    Im not sure if any of that is right - just guessing from seeing other code...

    Any suggestions?
    Thanx!

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    first of all, I would change the @@identity to scope_identity(). This prevents that you get the wrong id returned if someone else has done an insert as well. Look in BOL for more info on the differencences between the two.
    If you want to roll back all statements in the procedure, you have to use an explicit transaction.
    Code:
    create procedure register @params
    as
    set nocount on
    declare @id int
    declare @err int
    begin transaction
    insert into tbl_user ...
    set @err = @@error
    if @err <> 0
      begin
         'error'
      end
    else begin
      set @id = scope_identity()
      insert into tbl_user_details
      @err = @@error
    end
    
    if @err <> 0 
      begin
        rollback transaction
      end
    else
      begin
        commit transaction
      end
    Johan

  3. #3
    Join Date
    Aug 2003
    Location
    South Africa
    Posts
    8

    Smile

    Thanx jora!

Posting Permissions

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