Just like in Oracle SQL server allows the use of a catch block which can be very useful for debugging stored procedures!

First set up a transaction:

IF @@TRANCOUNT = 0
BEGIN TRANSACTION B4UPDATE
ELSE
SAVE TRANSACTION B4UPDATE

Then catch any errors and rollback if needed saving data about the issue.  If you also keep a variable with a step number you can also output this to an error table giving you the position where the code crunched!

BEGIN CATCH
ROLLBACK TRANSACTION B4UPDATE;
INSERT INTO ERRORS_SQL (TIMESTAMP, PROC_NAME, COMMENTS) VALUES (GETDATE(),‘BADGE_ALLOCATE_USEID’,‘errno: ‘ + ltrim(str(error_number())) + ‘ errmsg: ‘ + error_message())
COMMIT
END CATCH