CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012transactions
Marcin Bator
Marcin Bator

Reputation: 381

Rollback sql transaction only if unhandled error occurs

I am creating a database update mechanizm that is running multiple sql scripts in a transaction and, if all of them succeed, the update is commited.

The problem I am facing is that in every script there may be errors that are being handled and are not actually considered errors.

Example:

Script has to create a table and insert a row to the table, but the table already exists. It is okay, script should go on and try to insert the row, if the row exists - it is also okay and it should finish with no errors and move to next script. On the other hand - if there is an unexpected error (for example table exists, but has less columns so insert fails) it should throw an error and rollback transaction.

Because SQL Server rolls back my transaction on every error (regardless of whether it is handled or not), I am not able to perform updates correctly.

Example script would look something like this:

USE [Database]

BEGIN TRY

-- CREATING TABLE
CREATE TABLE [dbo].[Users](
    [UserId] [int] UNIQUE NOT NULL,
    [UserFullName] [nvarchar](100) NOT NULL,
    [UserName] [nvarchar](100) NOT NULL,
    [UserShortName] [nvarchar](50) NOT NULL,
    [UserLogin] [varchar](50) NOT NULL
)

PRINT 'Created table'
END TRY
BEGIN CATCH
    IF(ERROR_NUMBER() = 2714)
    BEGIN
        PRINT 'Table exists, proceeding to insert'
    END
    ELSE
        THROW
END CATCH

-- INSERTING USER
BEGIN TRY
INSERT INTO [dbo].[Users](
    [UserId]
    ,[UserFullName]
    ,[UserName]
    ,[UserShortName]
    ,[UserLogin]
) VALUES (
    1,'System Administrator','Admin','SA',  'SA'
)
PRINT 'Inserted user'
END TRY
BEGIN CATCH
    IF(ERROR_NUMBER() = 2627)
    BEGIN
        PRINT 'User exists - nothing to insert'
    END
    ELSE
        THROW
END CATCH
GO
PRINT '-- FINISHED add_users_table.sql'

(The script is just an example so it may not be 100% correct)

If table or row exist, transaction should continue, but if different errors are thrown it should rollback.

Is there a way to do this?

Thanks!

EDIT:

I forgot a crucial part of the mechanizm. I am running these scripts from C# (It is more like a pseudocode than actual code and I am also aware that GO command in SqlCommand will not work, but it has nothing to do with the problem):

SqlTransaction transaction = 
connection.BeginTransaction(IsolationLevel.ReadUncommited);

SqlCommand command = new SqlCommand("");
command.Connection = connection;

foreach(string script in scripts)
{
    command.CommandText = script;
    try
    {
        command.ExecuteNonQuery();
    }
    catch()
    {
        transaction.Rollback();
    }
}
transaction.Commit();

Upvotes: 2

Views: 1558

Answers (2)

dybzon
dybzon

Reputation: 1594

First of all, I agree that the solution suggested by SqlZim would probably be the way to go.

I'd like to add a bit of information from Microsoft's docs which I think explains quite well that you cannot do this:

If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. [...]. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION.

In other words; when your script fails it causes the transaction to go into an uncommittable state. The transaction must be rolled back - it cannot be committed. Since all the statements in your script are executed within the same transaction, they must all be rolled back.

Upvotes: 2

SqlZim
SqlZim

Reputation: 38023

It seems simpler to me to just check if the object exists, or the user exists prior to running the insert, e.g.:

dbfiddle.uk demo - if exists

if not exists (select 1 from sysobjects where name=N'Users' and xtype='U')
begin;
CREATE TABLE [dbo].[Users](
    [UserId] [int] UNIQUE NOT NULL,
    [UserFullName] [nvarchar](100) NOT NULL,
    [UserName] [nvarchar](100) NOT NULL,
    [UserShortName] [nvarchar](50) NOT NULL,
    [UserLogin] [varchar](50) NOT NULL
);
PRINT 'Created table'
end;

if not exists (select 1 from dbo.users where userlogin = 'SA')
begin;
INSERT INTO [dbo].[Users](
    [UserId]
    ,[UserFullName]
    ,[UserName]
    ,[UserShortName]
    ,[UserLogin]
) VALUES (
    1,'System Administrator','Admin','SA',  'SA'
)
PRINT 'Inserted user'
end;

Upvotes: 3

Related Questions