CODEWITHSUNDEEP
sql-serversql-server-2008pentaho-data-integration
xargs_so
xargs_so

Reputation: 55

SQL Server stored procedure to run insert/update in transaction

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

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?

Upvotes: 0

Views: 1576

Answers (1)

user3551009
user3551009

Reputation: 174

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

Upvotes: 1

Related Questions