Insert record in database table based on another column value in SQL Server

Refer below sample query.

SQL

CREATE PROCEDURE ValidteInsertUpdate
    @Id INT,
    @EffectiveDate VARCHAR(20)
AS
BEGIN
    IF EXISTS (SELECT * FROM Employees WHERE EmployeeId = @Id AND Effdt = @EffectiveDate)
    BEGIN
        DECLARE @SequenceExist INT
		SET @SequenceExist = (SELECT TOP 1 EffSequence FROM Employees WHERE EmployeeId = @Id ORDER BY Effdt DESC)
        INSERT INTO Employees (EmployeeId,Effdt,EffSequence) VALUES (@Id,@EffectiveDate,@SequenceExist + 1)
    END
	ELSE
	BEGIN
		INSERT INTO Employees (EmployeeId,Effdt,EffSequence) VALUES (@Id,@EffectiveDate,0)
	END

	DECLARE @Date VARCHAR(20),@Sequence INT
    SET @Date = (SELECT TOP 1 Effdt FROM Employees WHERE EmployeeId = @Id ORDER BY Effdt DESC)
	SET @Sequence = (SELECT TOP 1 EffSequence FROM Employees WHERE EmployeeId = @Id ORDER BY Effdt DESC)
    INSERT INTO Employees (EmployeeId,Effdt,EffSequence) VALUES (@Id,@Date,@Sequence + 1)
END