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

Alejandro Teixeira Muñoz · Accepted Answer · 2019-10-29 14:24:26Z

12

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://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-2017

Hope it helps :)

edited Oct 29, 2019 at 14:24

Alejandro Teixeira Muñoz

2,7881 gold badge24 silver badges31 bronze badges

answered Apr 29, 2019 at 10:30

HOUSSEM SABBEGH

1211 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alejandro Teixeira Muñoz Over a year ago

nice trick. Perfect for my case (lot of table changes. and for me with try catch has no sense. I just need kind of "please continue executing no matter of errors" and this is perfect

HashPsi · Accepted Answer · 2015-07-28 01:13:57Z

5

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

edited Jul 28, 2015 at 1:13

answered Jul 28, 2015 at 0:57

HashPsi

1,3912 gold badges12 silver badges22 bronze badges

3 Comments

Developer Nation Over a year ago

tried it but it is not working. the error message remains there without printing the message "error altering column R1 of Table2"

Developer Nation Over a year ago

@FelixPamittan i tried making a new database with no tables in it and used one of your try-catch but it does not print your error message and instead prints system default message

pro_gamer Over a year ago

@DeveloperNation the error is not caught because the severity of the error must be lower than what the catch block should handle. refer to learn.microsoft.com also referred by Sabbegh Houssem in his answer

Cedar · Accepted Answer · 2019-10-23 18:25:59Z

If Table2 does not have column R1 then the statement 'Alter Table Table2 ALTER COLUMN R1 varchar(MAX);' will not be a valid sql statement so the statement will not try to run so the catch will not happen. If instead you execute the statement it will try to run and the catch will work.

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

Pedro · Accepted Answer · 2020-08-06 19:16:50Z

-1

I have noticed that we get the desired behavior if the commands are run from inside an open session.

mysql -u root -p myDB < commands.sql >> stops on the first error

mysql -u root -p myDB
(after login)
source commands.sql

^^^ continues even after errors occur

answered Aug 6, 2020 at 19:16

Pedro

3831 gold badge4 silver badges19 bronze badges

1 Comment

Thomas Levesque Over a year ago

The question is about SQL Server, not MySQL

oscar · Accepted Answer · 2025-01-22 14:35:26Z

-1

You can use DataGrip of JetBrains, and choose the error option 'Ignore all' as shown in the image. When you end that group of executions and try with others the error will appear and the execution will stop again in the first error. so you do not need to worry if the configurations are saved

answered Jan 22 at 14:35

oscar

114 bronze badges

Collectives™ on Stack Overflow

SQL - Continue running all SQL statements even after error

Possible Error

Need

My Approach

5 Answers 5

1 Comment

3 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Possible Error

Need

My Approach

5 Answers 5

1 Comment

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related