Msg 266, Level 16, State 2,

Question

create or alter proc IngresarNuevosEmpleados
@apeEmp nvarchar(20)=null,
@nomEmp nvarchar(10)=null,
@cargo nvarchar(30)=null,
@NAC datetime=null,
@pais nvarchar(15)=null
as
begin

begin try  
	  
 if @apeEmp is null	or @nomEmp is null or @cargo is null or @nac is null or @pais is null  
begin  
	raiserror('Ingresar Datos Completos',10,1)  
	return  
end  
else if @pais = 'Argentina'	  
begin  
	raiserror('Ingrese un Pais Diferente',10,1)  
	return  
end  
else if year(@nac) > 1997  
begin  
	raiserror('Ingrese un año menor',10,1)  
	return  
end  
		begin tran Ingresar  
			insert into Empleado values (@apeEmp, @nomEmp,@cargo,@nac,@pais)  
			if not exists(select @nomEmp from Empleado where nomEmp = @nomEmp)  
			commit tran Ingresar  
			else  
			rollback tran Ingresar   
			  
end try   
begin catch   
	print 'Transaccion Cancelada'  
end catch  

end
go

Answer 1

Msg 266

= Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = %ld, current count = %ld.

You start an explicit transaction If the INSERT statement fails, the SP steps into the CATCH with only a PRINT statement; no ROLLBAKC and so you have at the end of SP a still open transact; for the rest see the error message.
Add a ROLLBACK in case of @@TRANCOUNT <> 0.
https://learn.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql?view=sql-server-ver16

Answer 2

Hi @jose jhoan fernadez Vasquez
You may need SP like this:

alter PROCEDURE spTest  
AS  
BEGIN  
    BEGIN TRY  
       BEGIN TRANSACTION  
          RAISERROR('', 16, 1)  
       COMMIT TRANSACTION  
    END TRY  
    BEGIN CATCH  
     --rollback   
     IF @@TRANCOUNT > 0  
    BEGIN  
       PRINT @@TRANCOUNT  
       ROLLBACK  
    END  
    END CATCH  
END  
GO  

Refer to this similar thread for more details: Rollback transaction from called stored procedure

Best regards,
LiHong