locked
How to insert data into multiple table at time in Single SP with transaction RRS feed

  • Question

  • User-1909855381 posted

    Hello,

    Below Query is the right way of inserting data into multiple tables, if not please suggest which one is the batter for this.

    CREATE PROCEDURE [dbo].[spUsers]
    @numUserId as numeric(18,0) = 0, 
    @vcFirstName as varchar(50) = NULL,
    @vcLastName as varchar(50) = NULL,
    @CreatedBy as numeric(18,0) = 0,
    @CreatedOn as datetime = NULL,
    @vcCountry as varchar(50) = NULL,
    @vcState as varchar(20) = NULL,
    @isVisbale as bit = 0
    AS
    BEGIN
        BEGIN TRY
    	BEGIN TRAN
    
    	       UPDATE tblUser
    		   SET
    		   vcFirstName = @vcFirstName,
    		   vcLastName = @vcLastName,
    		   CreatedBy = @CreatedBy,
    		   CreatedOn = @CreatedOn
    		   FROM tblUser WHERE numUserId = @numUserId
    
    		   UPDATE tblAddress
    		   SET
    		   vcCountry = @vcCountry,
    		   vcState = @vcState,
    		   CreatedBy = @CreatedBy,
    		   CreatedOn = @CreatedOn
    		   FROM tblAddress WHERE numUserId = @numUserId
    
    		   COMMIT TRAN
    	END TRY
    	BEGIN CATCH
    		ROLLBACK TRAN
    	END CATCH
    END
    
    
    


    But in this procedure transaction is not rollback when the update failed in the address table.

    Here are the Table Schema,

    Thank you,

    Wednesday, August 5, 2020 3:06 PM

All replies

  • User753101303 posted

    Hi,

    For example when which failure happens? Do you call this from within another transaction? (from C# or another SP maybe ?)

    I keep using https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-ver15 unless I want something more than just a rollback when an "exception" happens.

    Wednesday, August 5, 2020 4:07 PM
  • User475983607 posted

    santosh1502

    But in this procedure transaction is not rollback when the update failed in the address table.

    Can you explain what "failed" means?  You did not share all the schema which makes code shown confusing and problematic.  It looks like a one-to-many relationship between tblUser and tblAddress.  With that in mind, if the code executes without an exception, it will update every address the user has in the address table.  If this is a one-to-one relationship then there is no reason for the numAddressId just use the user id.  

    Wednesday, August 5, 2020 4:32 PM
  • User-1909855381 posted

    There is no relation between the table UserId only primary key,

    The issue is when I try to update data to bother table if there is not found userId in the Address table then updatable happened in User Table only because userId data found in User table,  

    At that time RollBack is not working when the userId not found in address table( Not to be updated if any of the table userid not found )

    exec spUsers 1,'RahulUpdate','KumarUpdate',2,getdate,'india','karnataka',0

    when i execute the store procedure data is being updated in the user table only, I don't want to update that if any of the table is not found userId.

     

    Wednesday, August 5, 2020 5:24 PM
  • User475983607 posted

    santosh1502

    The issue is when I try to update data to bother table if there is not found userId in the Address table then updatable happened in User Table only because userId data found in User table,  

    The code you've shown works as expected.  I think you made an assumption that the second query throws an exception if a record is not found.  That's no how filters work in SQL.   Write code that checks if numUserId exists in both tables before executing the UPDATE.  

    https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15

    I think it is important to understand that using TRY...CATCH as part of logical flow, like you are doing, is not a good design.

    CREATE PROCEDURE [dbo].[spUsers]
    @numUserId as numeric(18,0) = 0, 
    @vcFirstName as varchar(50) = NULL,
    @vcLastName as varchar(50) = NULL,
    @CreatedBy as numeric(18,0) = 0,
    @CreatedOn as datetime = NULL,
    @vcCountry as varchar(50) = NULL,
    @vcState as varchar(20) = NULL,
    @isVisbale as bit = 0
    AS
    BEGIN
        BEGIN TRY	
            IF(EXISTS (SELECT (1) FROM tblUser WHERE numUserId = @numUserId) 
                AND EXISTS(SELECT (1) FROM tblAddress WHERE numUserId = @numUserId))
            BEGIN
               BEGIN TRAN
            
    	       UPDATE tblUser
    		   SET
    		   vcFirstName = @vcFirstName,
    		   vcLastName = @vcLastName,
    		   CreatedBy = @CreatedBy,
    		   CreatedOn = @CreatedOn
    		   FROM tblUser WHERE numUserId = @numUserId
    
    		   UPDATE tblAddress
    		   SET
    		   vcCountry = @vcCountry,
    		   vcState = @vcState,
    		   CreatedBy = @CreatedBy,
    		   CreatedOn = @CreatedOn
    		   FROM tblAddress WHERE numUserId = @numUserId
    
    		   COMMIT TRAN
            END
    	END TRY
    	BEGIN CATCH
    		ROLLBACK TRAN
    	END CATCH
    END



    Wednesday, August 5, 2020 5:51 PM