How catch the db exception in stored procedure

[NeedSomeAnswers]

You need to use @@Error.

When you run an sql statement in a stored proc immediately afterwards check if @@Error equals Zero. If Not then the statement has errored.

Here is a basic example.

BEGIN TRAN

SELECT * FROM TEST

IF @myERROR != 0 GOTO HANDLE_ERROR

COMMIT TRAN

HANDLE_ERROR:

ROLLBACK TRAN