SQL - Continue running all SQL statements even after error

Question

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

Answer 1

You should use 'GO' between instructions in order to contine the execution no matter on the errors:

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

This will give you all messages and will execute all the sentences one after each other. Those are my logs on a similar situation. As you will see, various errors are notified, and not only one:

NOTE: The catch behavior depends on the severity of the error, this link from the MSDOC explains how try_catch works https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-2017

Hope it helps :)

Answer 2

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