I am currently typing a script for a project of mine and currently having difficulties finding information on how to do most of the functionality MS SQL has to offer.
I am trying to figure out a way to test if an SQL statement failed its execution. For example, making an attempt to insert a record that already exists in the table. Any help would be greatly appreciated
You can use the try/catch capabilities of SQL Server.
Another article source and sample T-SQL:
BEGIN TRY
DECLARE @X INT
-- Divide by zero to generate Error
SET @X = 1/0
PRINT 'Command after error in TRY block'
END TRY
BEGIN CATCH
PRINT 'Error Detected'
END CATCH
E.g. Handle failures in the catch block for specific operations.
Try catch is the way to go. If you have many steps in a porcess and want to see details of what it did at some furite date including the errors, you can store the details you need after each step in a table variable as you go (And the error in the catch block) and then after the rollback, insert the details into a permananet logging table. This will give you a history of errors whch can be invaluable in researching problems later. Table variables do not go out of scope in a rollback while temp tables would.
