-- STRUCTURED EXCEPTION HANDLING is where you create a structure to intercept and handle errors. This is often done with a TRY block that tries to do something, and then you have a CATCH block that catches the error and then handles the error in the case that something fails during the TRY block. Common CATCH block techniques are to put in code that logs what the error was, and/or tries to fix the error, and/or tries to re-THROW the error so that any application you're using can do it's own error handling (ie expose the error to SLQ Server so it can do it's normal error handling), or THROW a custom error message that says something like 'an error occurred but it's been taken care of and you're good'.

---------------------------------------------------------------------------------------------------
-- Simple TRY and CATCH example. CATCH block only runs if there's an exception in the TRY block. --
---------------------------------------------------------------------------------------------------

-- begins the TRY block
BEGIN TRY
	UPDATE Production.Product
	-- statement that will intentially divide by zero and therefore create an error
	SET PRICE = Price / 0
END TRY
-- begins the CATCH block that will run if an error results from the TRY block
BEGIN CATCH
  -- prints the following text
	PRINT 'The following error occurred';
	--Function that prints the error message encountered
	PRINT ERROR_MESSAGE();
	-- THROWs an error message
	THROW 50001, 'An error occurred', 0;
END CATCH;

/*

Result from query above - error is THROWN to the user, but not exposed to the application:

(0 row(s) affected)
The following error occurred:
Divide by zero error encountered.

*/

------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Example that handles the error and then re-THROWs the error so it's exposed to SQL Server and SQL Server can then go through its usual error handling. --
------------------------------------------------------------------------------------------------------------------------------------------------------------

-- Begins TRY block
BEGIN TRY
	UPDATE Production.Product2
	-- statement that will intentionally cause a divide by zero error
	SET StandardCost = ProductID / 0;
END TRY
-- Begins CATCH block which will execute if there's an error in TRY block
BEGIN CATCH
	-- Prints 'The following error occurred:'
	PRINT 'The following error occurred:';
	-- Prints the error message encountered so user can see it.
	PRINT ERROR_MESSAGE();
	-- THROW by itself will THROW the currently outstanding error (rethrowing the error so the client application can get it and do any of it's own error handling - the client application will only see the error produced by the THROW, it will not see or handle the error message produced by the CATCH block).
	THROW;
END CATCH;


/*

Result from query above:

(0 row(s) affected)
The following error occurred:
Divide by zero error encountered.
Msg 8134, Level 16, State 1, Line 6
Divide by zero error encountered.

DETAIL OF THE RESULTS ABOVE
(0 row(s) affected)
-- error message from the CATCH block, seen and handled by the user but not the client application
The following error occurred:
Divide by zero error encountered.
-- error message from the THROW, seen and handled by the client application (SQL Server)
Msg 8134, Level 16, State 1, Line 6
Divide by zero error encountered.

*/

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- example of more complicated version that CATCHes error and writes it to the AdventureWorks dbo.ErrorLog table, using the pre-existing dbo.uspLogError stored procedure. --
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- begins TRY block
BEGIN TRY
	UPDATE Production.Product2
	-- statement that will intentionally cause a divide by zero error
	SET StandardCost = ProductID / 0;
END TRY
-- begins CATCH block which will execute if there's an error in TRY block
BEGIN CATCH
	-- delcares variables @ErrorLogId to hold ID, @ErrorMsg to hold message
	DECLARE @ErrorLogID AS INT, @ErrorMsg AS VARCHAR(250);
	-- pre-existing dbo.uspLogError stored procedure that logs the error into pre-existing table dbo.ErrorLog with @ErrorLogID as an OUTPUT parameter
	EXECUTE dbo.uspLogError @ErrorLogID OUTPUT;
	-- sets error message to this text, inserting @ErrorLogID so the user can see which ErrorLogID to look at in dbo.ErrorLog for more information
	SET @ErrorMsg = 'The update failed because of an error. View error #'
		+ CAST(@ErrorLogID AS VARCHAR)
		+ ' in the error log for details.';
	-- THROWs custom error showing user contents of @ErrorMsg variable
	THROW 50001, @ErrorMsg, 0;
END CATCH;