CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
mark
mark

Reputation: 62722

How to make an error propagate out of EXEC and abort the rest of the script in T-SQL?

Consider the following trivial T-SQL:

EXEC('SELECT 1/0')
EXEC('SELECT ''a''')

It still executes the second EXEC statement. How do I abort the entire script upon the first failure?

Upvotes: 1

Views: 268

Answers (1)

artm
artm

Reputation: 8584

Put it inside a try/catch:

BEGIN TRY
    EXEC('SELECT 1/0')
    EXEC('SELECT ''a''')
END TRY
BEGIN CATCH
    SELECT 
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() as ErrorState,
    ERROR_PROCEDURE() as ErrorProcedure,
    ERROR_LINE() as ErrorLine,
    ERROR_MESSAGE() as ErrorMessage;
END CATCH;

https://msdn.microsoft.com/en-us/library/ms179296(v=sql.105).aspx

Upvotes: 1

Related Questions