Your Sequence procedure is badly written. What is your SQL Server version? Perhaps you just can use SEQUENCE instead?

Don't change transaction isolation level inside that procedure.

Here is how our similar procedure is written (also not the best way, but better):

/*
**	Retrieve a new unique id from the sequence table
*/
ALTER procedure [dbo].[siriussp_get_unique_key2]
      @tcKeyName varchar(50),
      @NumberOut int output
as
   set nocount on
   declare @nValue int
                    
   update sequence
      set @nValue = next_val, next_val = next_val + 1
    where name = @tcKeyName
                 
   if @nValue is null
      begin
         set @nValue = 1
         insert into sequence (name, next_val)
                       values (@tcKeyName, 2)
      end
      
   set @NumberOut = @nValue

>Hello, I have the following problem'm doing a stored procedure in which I need to get another sequence which I check sp in which was the last record but it is throwing errors 50000 and I do not know why?
>
>
>

>declare @idingreso int=0,@docu varchar(12)='',@prestamo int=0,@cliente int=0,@fecha datetime,@monto numeric(12,2)=0,
>@concepto varchar(150)='',@capital numeric(12,2)=0.00,@interes numeric(12,2)=0.00,@mora numeric(10,2)=0,
>@cnttransa int=0,@ncf varchar(19)='',@prestamoid varchar(9)='',@cobrador smallint=2,@sec int=0
>Declare @tablevar table(secux int)
>
>set @monto=2000
>set @prestamo=10
>set @capital=1500
>set @interes=500
>set @cliente=5
>set @fecha=GETDATE()
>
>
>insert @tablevar(secux) EXEC [P_secuencias]'1402','1'
>select @idingreso=secux from @tablevar
>set @docu='RI'+RIGHT('0000000000'+RTRIM(cast(@idingreso as varchar(9))),10)
>
>insert Into HINGRESOS (idingreso,idtiptra,documento,idprestamo,idcliente,fecha,monto,concepto,capital,interes,mora,
>efectivo,creado,cnttransaid,tiponcfid,ncf,prestamoid,cobradorid) values
>(@idingreso,'RI',@docu,@prestamo,@cliente,@fecha,@monto,@concepto,@capital,@interes,@mora,@monto,'jose',@cnttransa,'02',@ncf,@prestamoid,@cobrador)
>
>
>
>
>---procedure secuencias
>
>
>/****** Object:  StoredProcedure [dbo].[P_SECUENCIAS]    Script Date: 10/10/2016 11:51:17 ******/
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>ALTER PROCEDURE [dbo].[P_SECUENCIAS]@movi char(4),@crear bit
>AS
>BEGIN
>	Set Nocount On
>	Declare @Ret INT,@cTabla Varchar(255),@nuevoid int,@secuencia int ,@sec int
>	--set @sec=@@TRANCOUNT
>	Set Transaction Isolation Level Read Uncommitted 
>	set @secuencia=0
>	if @crear=1
>	begin
>		IF @@Trancount > 0
>			BEGIN
>				Raiserror('Error: En transaccion.', 1, 16)
>				Return -998
>			END 
>		-- Verificar si existe la tabla
>		IF Exists(SELECT tipomoviid FROM secuencias WHERE tipomoviid=@movi) 
>			BEGIN -- Si ya existe, se obtiene el valor generado
>				UPDATE secuencias set secuenciaid =secuenciaid + 1 WHERE tipomoviid=@movi
>			END
>		ELSE
>			BEGIN -- Si no existe, se genera la nueva tabla
>				SELECT @NuevoId = 1
>				INSERT secuencias (secuenciaid,tipomoviid) VALUES (@NuevoId,@movi)
>			END 
>		-- Control de errores
>		SELECT @ret = @@error
>		IF @ret <> 0
>			BEGIN
>				Raiserror('Error en la obtención de nuevo identificador', 1, 16)
>				Set Nocount Off
>				Return @ret
>			END
>	end
>	set @secuencia=isnull((select secuenciaid from secuencias WHERE tipomoviid=@movi),0)
>	select @secuencia as secu
>	RETURN 0
>END 
>
>
>
>
>
>
>
>
>