Example Improper error handling with SQL try catch

CatchWillFailWhenUsedInProcedure.sql

-- This procedure will fail twice, first time in main TRY block and second time in CATCH statement because of converting ERROR_MESSAGE to INT
CREATE PROCEDURE dbo.SomeWork
AS
BEGIN TRY
	-- let's do something
	--  ...
	-- Exception happens
	PRINT 0/0
END TRY

BEGIN CATCH
	DECLARE @ErrorMessage VARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SELECT 
		@ErrorMessage = ERROR_MESSAGE() + ' Procedure '+ ERROR_PROCEDURE() + ' Line: '+ERROR_LINE(),
		@ErrorState = ERROR_STATE();
	RAISERROR (@ErrorMessage,16,@ErrorState);
END CATCH
GO
EXEC dbo.SomeWork
-- Output:
-- Msg 245, Level 16, State 1, Procedure SomeWork, Line 14
-- Conversion failed when converting the nvarchar value 'Divide by zero error encountered. Procedure SomeWork Line: ' to data type int.

TryCatchWorksOutsideOfProcedure.sql

-- Running same code out of procedure will work (read: it will fail only once), because ERROR_PROCEDURE is NULL and entire evaluation of @ErrorMessage will result in NULL
BEGIN TRY
	-- let's do something
	--  ...
	-- Exception happens
	PRINT 0/0
END TRY

BEGIN CATCH
	DECLARE @ErrorMessage VARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SELECT 
		@ErrorMessage = ERROR_MESSAGE() + ' Procedure '+ ERROR_PROCEDURE() + ' Line: '+ERROR_LINE(),
		@ErrorState = ERROR_STATE();
	RAISERROR (@ErrorMessage,16,@ErrorState);
END CATCH
-- Output
-- Msg 50000, Level 16, State 1, Line 16

http://paps-tech.blogspot.com/2015/03/sql-code-can-behave-differently-in.html