alter PROCEDURE spGetProximoClienteTango

@codigoInicial VARCHAR(6),

@id_vendedor int,

@id_cliente int,

@codigoFinal VARCHAR(6) OUTPUT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

SET @codigoFinal=''

DECLARE @ultimoCodigo AS VARCHAR(6)

SELECT TOP 1 @ultimoCodigo=cod_client from gva14 where cod_client like '' + @codigoInicial + '%' + '' and dbo.isinteger(substring(cod_client,LEN(@codigoinicial)+1,6))=1 order by cast(substring(cod_client,LEN(@codigoinicial)+1,6) as integer) desc

IF @ultimoCodigo<>''

begin

declare @i INTEGER

DECLARE @longitud INTEGER

SET @longitud=LEN(@ultimoCodigo)

set @i=@longitud

while isnumeric(substring(@ultimoCodigo,@i,1))=1 and @i>=0

begin

set @i=@i-1

END

declare @ultimoNumero int

IF @i=0 

BEGIN

set @ultimoNumero = cast(substring(@ultimoCodigo,@i+1,999) as int)

set @ultimoNumero=@ultimoNumero+1

--set @codigoFinal=CAST(@ultimoNumero AS VARCHAR)

set @codigoFinal=SUBSTRING('000000',0,@longitud-LEN(@ultimoNumero)-@i+1) + CAST(@ultimoNumero AS VARCHAR)

end

ELSE

begin

set @ultimoNumero = cast(substring(@ultimoCodigo,@i+1,999) as int)

set @ultimoNumero=@ultimoNumero+1

set @codigoFinal=substring(@ultimoCodigo,0,@i+1)+ SUBSTRING('000000',0,@longitud-LEN(@ultimoNumero)-@i+1) + cast(@ultimoNumero as VARCHAR)

END

end

return

END