demo

amended.sql

USE [Unicorn Paddock]
GO
/****** Object:  StoredProcedure [dbo].[sp_test]    Script Date: 21/06/2019 11:57:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_test]
AS
BEGIN

    begin transaction
		begin try

			declare @name varchar(50)

			select @name=[name] from Unicorns where [id]=23; 

			if nullif(@name,'') = '' 
			begin
				raiserror('Name cannot be null or empty', 5, 1)
			end
	
			-- continue 
			-- ...
		end try
		begin catch
			declare @errorMessage nvarchar(4000);
			declare @errorSeverity int;
			declare @errorState int;

			select	@errorMessage=ERROR_MESSAGE(), 
					@errorSeverity=ERROR_SEVERITY(), 
					@errorState=ERROR_STATE()

			--insert into ErrorLog select 1,@errorSeverity,@errorState,'sp_test',30,@errorMessage,getdate(); 	
			exec Logging...sp_log 1,@errorSeverity,@errorState,'sp_test',30,@errorMessage; 	
			rollback transaction
		end catch
	
	commit transaction
	
END