Rollback sql transaction only if unhandled error occurs

[zwirek2201]

Hi!

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();

 

[kladzen]

you really should use the build in function of t-sql with transaction and how to handle them instead of having C# do it.

 

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql <---- read this and the few other pages(left side) on how to commit a transaction and perform rollback of these transactions.