How can SQL code behave differently in or outside procedure and about poor error handling

During refactoring of very old code, I found a ancient/ugly and in the end even totally wrong error handling script. Interesting is that the developer probably tested it outside of procedure and it did "kind a work" for him, ... :)

I'll add details later (or much later).

	-- 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.

view raw CatchWillFailWhenUsedInProcedure.sql hosted with ❤ by GitHub

	-- 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

view raw TryCatchWorksOutsideOfProcedure.sql hosted with ❤ by GitHub

How can I query remote Microsoft SQL Server using integrated security

How can I query remote Microsoft SQL Server without setting up linked server and not using hardcoded username and password of course! :)

	-- Using OPENROWSET
	SELECT m.name, m.database_id
	FROM OPENROWSET('SQLNCLI', 'Server=RemooteServerName;Trusted_Connection=yes;','SELECT * FROM master.sys.databases') AS m
	-- Using OPENDATASOURCE
	SELECT name, database_id
	FROM OPENDATASOURCE('SQLNCLI','Data Source=RemooteServerName;Integrated Security=SSPI').master.sys.databases

view raw MSSQLRemoteServerQuery.sql hosted with ❤ by GitHub