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?

Firstly, `select max(id) + 1` is a bad idea - I suggest you use `identity` or a `sequence`. Secondly here is one of many solutions to this: https://stackoverflow.com/questions/1749719/sql-server-transactions-roll-back-on-error — Nick.Mc, Oct 06 '18 at 10:38

score 1 · Accepted Answer · answered Oct 06 '18 at 10:37

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

answered Oct 06 '18 at 10:37

user3551009

174
6

is it possibile to create a procedure that accept a generic text input and run it like a sql instruction? so i can use the same procedure to call all of my blocks. – xargs_so Oct 06 '18 at 18:26
I suggest you have the decency to try (and if suitable, accept) this question rather than just shooting off a new question. What you want to do is a bad idea and if you want to find out why, ask it in a new question. – Nick.Mc Oct 07 '18 at 00:50

SQL Server stored procedure to run insert/update in transaction

1 Answers1