SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO
alter procedure dbo.Test1_Notrans
as
begin

SET NOCOUNT ON

declare @$prog varchar(50), 	@$errno int, 	@$errmsg varchar(4000), 
	@$proc_section_nm varchar(50),	@$row_cnt INT,
	@$error_db_name varchar(50), 
	@$CreateUserName varchar(128),   --last user changed the data 
	@$CreateMachineName varchar(128); --last machine changes	@$CreateSource varchar(128) --last process that made a changes

	-- additional for non trans proc if a wrapper for transactional
	declare @$tran_flag int, @$tran_flag_err int;
	set @$tran_flag = @@trancount;

select @$errno = NULL,  @$errmsg = NULL,  @$proc_section_nm = NULL
, @$prog = LEFT(object_name(@@procid),50),@$row_cnt = NULL, @$error_db_name = db_name();
--=========
BEGIN TRY
--=========

select top 3 * 
from Northwind.dbo.Employees

exec @$errno = dbo.Test2_trans

--========
END TRY

--========
BEGIN CATCH
--===========
-- additional for non trans proc if a wrapper for transactional
set @$tran_flag_err = @@trancount;

IF (@$tran_flag = 0 and @$tran_flag_err > 0 )
		rollback;

-- set error number if not set
IF ISNULL(@$errno,0) = 0
	Set @$errno = ERROR_NUMBER();

set @$errmsg = Left('Error ' +
		CASE
			WHEN @$errno > 0 THEN CAST(@$errno as varchar)
			ELSE Cast(ERROR_NUMBER() as varchar)
		END + 'in proc ' + isnull(@$prog,' ') + ' ' + 
		CASE 
			WHEN @$errno > 0 THEN isnull(@$errmsg,' ') 
			ELSE isnull(@$errmsg,' ') + ISNULL(ERROR_MESSAGE(),'')
		END ,4000);

raiserror (@$errmsg, 16, 1); 

EXEC dbo.ERROR_LOG_2005 @ERROR_LOG_PROGRAM_NM  = @$prog,  
		@ERROR_LOG_PROGRAM_SECTION_NM  = @$proc_section_nm,
		@ERROR_LOG_ERROR_NO  = @$errno,  
		@ERROR_LOG_ERROR_DSC  = @$errmsg,
		@ERROR_DB_NAME  = @$error_db_name;
END CATCH

SET NOCOUNT OFF; 

return @$errno;  

end