Msg 266, Level 16, State 2,

jose jhoan fernadez Vasquez 1 Reputation point
2022-06-23T03:28:26.563+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,237 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,515 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 38,786 Reputation points
    2022-06-23T09:44:37.587+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

  2. LiHong-MSFT 10,036 Reputation points
    2022-06-24T02:06:44.743+00:00

    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

    0 comments No comments