IT:AD:SQL Server:HowTo:SQL:Functions/Try/Catch Functions

It's horribly verbose compared to some languages, but this is a simple example:

BEGIN TRY
BEGIN TRANSACTION

EXEC sp_SomeFaultCode


COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Execute error retrieval routine

-- Error_Message() : returns message typically sent to caller. 
-- Error_Number() : returns integer assigned to the error.
-- Error_Severity() : returns integer representing severity. 
-- Error_State() : returns the state. 
-- Error_Procedure() : returns procedure/function name (or null). 
-- Error_Line() : returns line number where the error occurred. 

SELECT 
@@ERROR,
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;

--OR: EXECUTE usp_GetErrorInfo;

ROLLBACK TRANSACTION

RAISERROR (N'This is message %s %d.', -- Message text.
10, -- Severity,
1, -- State,
N'number', -- First argument.
5); -- Second argument.

END CATCH;

* TryCatch: http://msdn.microsoft.com/en-us/library/ms175976.aspx * ErrorNumber: http://msdn.microsoft.com/en-us/library/ms175069.aspx * RaiseError: http://msdn.microsoft.com/en-us/library/ms178592.aspx * @@ERROR: http://msdn.microsoft.com/en-us/library/ms188790.aspx