Hi,
Please refer below code
SQL
CREATE Procedure InsertPerson
@Name VARCHAR(50)
BEGIN
Declare @CurDate DATETIME
SET @CurDate = GETDATE()
DECLARE @Id INT
SELECT @Id = ISNULL(MAX(Id),0) FROM tblPerson WHERE YEAR([Date]) = YEAR(@CurDate)
SET @Id = @Id + 1
IF DAY(@CurDate) = 1 AND MONTH(@CurDate) = 1
BEGIN
SET @Id = 1
END
INSERT INTO tblPerson(Id,Name,[Date]) VALUES(@Id,@Name,GETDATE())
END