SQL Server stored procedure to run insert/update in transaction

Question

I have to execute blocks of T-SQL with insert and update instructions.

Let's say I have these two blocks of code, and I have no direct control on them.

Block #1:

insert into mytable (id, col1) 
values ((select max(id) + 1 from mytable), 'foo');

insert into non_existing_table (id, col1)  
values ((select max(id) from mytable), 'bar');

Block #2:

insert into mytable (id, col1) 
values ((select max(id) + 1 from mytable), 'foo');

insert into anothertable (id, col1) 
values ((select max(id) from mytable), 'bar');

In the first block there are two instructions, the first is ok, the second one fails because the second table does not exists.

In the second block there are also two instructions, both are ok.

What I want to do is run both two blocks

• The first block should do nothing in the end, since the second instruction fails, the first should be rolled back
• The second block is fine, since there is no error, both inserts should be committed

What's the correct way to do this? Maybe a stored procedure that accepts a full text parameter and run all the code as a transaction? How can I do that in SQL Server?

Answer 1

You can create a stored procedure with transactions. Try like this.

CREATE PROCEDURE [dbo].[Procedure_name]
@iErrorCode int OUTPUT,
--other parameters
AS
BEGIN
   BEGIN TRY
     begin tran

        insert into mytable (id,col1) values ((select max(id)+1 from mytable),'foo');
        insert into non_existing_table (id,col1) values ((select max(id) from mytable),'bar');
        --other queries

     commit tran
     SELECT  @iErrorCode =@@ERROR;
   END TRY
   BEGIN CATCH
      rollback tran
      Select ERROR_NUMBER();
      Select ERROR_MESSAGE();
      SELECT  @iErrorCode =@@ERROR
   END CATCH
END