Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Posts
    11

    Unanswered: Ability to update multiple tables simultaneously via stored proc

    Anyone have any ideas on how to use a stored procedure to update multiple tables simultaneously? I am updating a parent record and zero or more child records. I would like to make one stored procedure call if possible to do so. Any ideas on doing this would be appreciated. Thanks!

    Everett
    Everett

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    You can update more than one table within a stored procedure, just make sure to use BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN

    Example:
    Code:
    CREATE PROCEDURE sp_ModifyMasterDetail
        (
        .
        .
        .
        @msg  varchar(255) output
        )
    AS
    SET NOCOUNT ON
    
    DECLARE @error      int
          , @tfTran     tinyint
    
    --
    --  Start Transaction
    --
    IF (@@TRANCOUNT = 0) BEGIN
        SELECT  @tfTran = 1
        BEGIN TRAN
    END
    ELSE
        SELECT  @tfTran = 0
    
    
    UPDATE tblMaster
          .
    WHERE ID = @ID
    
    SELECT  @error = @@error
    
    IF (@error <> 0)
        GOTO Error_Exit
    
    UPDATE tblDetail
          .
    WHERE ID = @ID
    AND   SubID = @SubID
    
    SELECT  @error = @@error
    
    IF (@error <> 0)
       GOTO Error_Exit
    
    --
    --  Check to see if an error occured during processing.  If so then
    --  ROLLBACK else COMMIT transactions
    --
    Error_Exit:
    
    IF (@error <> 0) BEGIN
        IF (@tfTran = 1)
            ROLLBACK TRAN
    
        SELECT @msg  = 'ERROR: Transaction failed with error ' + CONVERT(varchar(20),@error),
    END
    ELSE BEGIN
        IF (@tfTran = 1)
            COMMIT TRAN
    
        SELECT @msg = 'Transaction successful'
    END
    
    RETURN @error
    GO

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    begin tran
    update parent set ...
    if @@error <> 0
    begin
    raiserror('failed',16,-1)
    rollback tran
    return
    end
    update child set ...
    if @@error <> 0
    begin
    raiserror('failed',16,-1)
    rollback tran
    return
    end
    commit tran

    Or you could put a trigger on the parent (or child) table or on a view of the combination - depends on the updates you want to do.

  4. #4
    Join Date
    Feb 2002
    Posts
    11
    Thanks guys! Either one of these will do the trick, except that I'm not sure how to get the data into the stored procedure! I guess I could munge it into varchar(8000), but I'm not sure that it would always be long enough. Any way of passing either an array or a recordset/cursor into a stored procedure?

    Everett
    Everett

  5. #5
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    You can create a temp table on the spid, populate it then access it in the SP.

    Call the SP repeated times with the values and the SP can populate a table keyed on spid.

    Call the sp with comma delimitted strings with the values.

    Have lots of parameters - up to the max you think you will need.

Posting Permissions

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