Search code examples
sqlsql-serversql-server-2008

SQL - Continue running all SQL statements even after error


I have some queries like this

Alter Table Table1 ALTER COLUMN T1 varchar(MAX);
Alter Table Table1 ALTER COLUMN T2 varchar(MAX);
Alter Table Table1 ALTER COLUMN T3 varchar(MAX);

--Table2 does not have a column "R1" and is likely to give error
Alter Table Table2 ALTER COLUMN R1 varchar(MAX);

Alter Table Table2 ALTER COLUMN T1 varchar(MAX);
Alter Table Table2 ALTER COLUMN T2 varchar(MAX);
Alter Table Table2 ALTER COLUMN T3 varchar(MAX);

Possible Error

Now in the 4th statement it is likely that a message would pop because there is no field in Table2 named R1.

Need

I need a way so that all the statement gets executed even after receiving the error.

My Approach

I tried to execute these statements individually to receive error message for every line but it takes too much time as it makes 7 times connection to a server which is connected to the PC by internet . So, i used all those query together to get records in one connection but it breaks the command on 4th line as the statement is invalid.


Any suggestion or piece of code is appreciated


Solution

  • Use a try-catch block:

    Alter Table Table1 ALTER COLUMN T1 varchar(MAX);
    Alter Table Table1 ALTER COLUMN T2 varchar(MAX);
    Alter Table Table1 ALTER COLUMN T3 varchar(MAX);
    
    BEGIN TRY
         Alter Table Table2 ALTER COLUMN R1 varchar(MAX);
    END TRY
    BEGIN CATCH
         print 'error altering column R1 of Table2';
    END CATCH;
    
    Alter Table Table2 ALTER COLUMN T1 varchar(MAX);
    Alter Table Table2 ALTER COLUMN T2 varchar(MAX);
    Alter Table Table2 ALTER COLUMN T3 varchar(MAX);