CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Steve
Steve

Reputation: 229

How to determine in-script if a SQL statement failed

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

Upvotes: 2

Views: 2191

Answers (2)

HLGEM
HLGEM

Reputation: 96640

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.

Upvotes: 1

Paul Sasik
Paul Sasik

Reputation: 81537

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.

Upvotes: 6

Related Questions