Handling Sql Server script errors gracefully

I think there's nothing we can do about Sql Server treatment with DDL error severity, some of it are handled automatically (forcibly rolling back transaction for example) by Sql Server itself.

What we can just do is make our script code cope around it and provide script users with descriptive error.

An example:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

--  drop table thetransformersmorethanmeetstheeye --  select * from thetransformersmorethanmeetstheeye       --  first batch begins here               begin tran                   create table thetransformersmorethanmeetstheeye(i int); -- non-erring if not yet existing           -- even there's an error here, @@ERROR will become 0 on next batch     ALTER TABLE [dbo].[Table1]  WITH CHECK ADD  CONSTRAINT [FK_constraint] FOREIGN KEY([field1], [field2])     REFERENCES [dbo].[Table2] ([field3], [field4]);                   go  -- first batch ends here       --  second batch begins here       if @@TRANCOUNT > 0 begin             PRINT 'I have a control here if things needed be committed or rolled back';                   -- @@ERROR is always zero here, even there's an error before the GO batch.         -- @@ERROR cannot span two batches, it's always gets reset to zero on next batch         PRINT @@ERROR;                                     -- But you can choose whether to COMMIT or ROLLBACK non-erring things here         -- COMMIT TRAN;         -- ROLLBACK TRAN;                   end     else if @@TRANCOUNT = 0 begin         PRINT 'Sql Server automatically rollback the transaction. Nothing can do about it';     end     else begin         PRINT 'Anomaly occured, @@TRANCOUNT cannot be -1, report this to Microsoft!';     end       --  second batch implicitly ends here