Según entiendo primero obtenes un correlativo de la tabla 1 y con esto buscas en la tabla 2 el lote con ese correlativo, para ver si ya existe ese correlativo, si es así lo que queres se debe de hacer así:
Código SQL:
Ver originalCREATE FUNCTION dbo.Maximo (@lote AS VARCHAR(20))
RETURNS INT AS
BEGIN
DECLARE @correlativo1 AS INT
DECLARE @correlativo2 AS INT
SELECT @correlativo1 = isnull(MAX(correlativo),0)
FROM TABLA_1
WHERE Lote = @Lote
SELECT @correlativo2 = isnull(MAX(correlativo),0)
FROM TABLA_2
WHERE Lote = @Lote
AND correlativo = @correlativo1
RETURN (@correlativo2)
END
La función te retorna el correlativo de la Tabla 2 a partir del lote y el correlativo.
Saludos.