How to debug a stored procedure

CREATE DATABASE TestScheduleDB;
GO

USE TestScheduleDB;
GO

CREATE TABLE dbo.ScheduleTemplateDetail (
  TemplateId int NOT NULL,
  DayOrder int NOT NULL,
  AbsenceCode varchar(25) NULL,
  WorkShiftCd varchar(25) NULL,
  PRIMARY KEY CLUSTERED (TemplateId, DayOrder)
)
GO

CREATE TABLE dbo.ScheduleDetail (
  ScheduleId int NOT NULL,
  DateOut date NOT NULL,
  TimeSheetDate date NOT NULL,
  AbsenceCode varchar(25) NULL,
  PRIMARY KEY CLUSTERED (ScheduleId, DateOut)
)

CREATE TABLE dbo.Schedule (
  ScheduleId int IDENTITY,
  TemplateId int NOT NULL,
  PRIMARY KEY CLUSTERED (ScheduleId)
)
GO

INSERT INTO ScheduleTemplateDetail (TemplateId, DayOrder, AbsenceCode, WorkShiftCd)
VALUES
(1, 1, 'Vacation', 'Morning'),
(1, 2, 'Sick Leave', 'Afternoon'),
(1, 3, 'Paid Leave', 'Night'),
(2, 1, 'Sick Leave', 'Morning'),
(2, 2, 'Vacation', 'Afternoon'),
(2, 3, 'Paid Leave', 'Night'),
(3, 1, 'Paid Leave', 'Morning'),
(3, 2, 'Sick Leave', 'Afternoon'),
(3, 3, 'Vacation', 'Night'),
(4, 1, 'Paid Leave', 'Morning');


INSERT INTO Schedule (TemplateId)
VALUES
(1),
(2),
(3),
(4),
(1),
(2),
(3),
(4),
(1),
(2);

INSERT INTO ScheduleDetail (ScheduleId, DateOut, TimeSheetDate, AbsenceCode)
VALUES
(1, '2025-04-01', '2025-04-01', 'Vacation'),
(2, '2025-04-01', '2025-04-01', 'Sick Leave'),
(3, '2025-04-01', '2025-04-01', 'Paid Leave'),
(4, '2025-04-01', '2025-04-01', 'Vacation'),
(5, '2025-04-01', '2025-04-01', 'Sick Leave'),
(6, '2025-04-01', '2025-04-01', 'Paid Leave'),
(7, '2025-04-01', '2025-04-01', 'Vacation'),
(8, '2025-04-01', '2025-04-01', 'Sick Leave'),
(9, '2025-04-01', '2025-04-01', 'Paid Leave'),
(10, '2025-04-01', '2025-04-01', 'Vacation');

CREATE PROCEDURE FillSchedule 
  @pDateFrom DATE,
  @pDateTo DATE,
  @pScheduleId INT
AS
BEGIN
  DECLARE @DateOut DATE;
  DECLARE @TemplateCount INT;
  DECLARE @RowNumber INT;
  DECLARE @TimeSheetDate DATE;

  -- Get templates
  SET @TemplateCount = (
    SELECT COUNT(*) 
    FROM Schedule s
    JOIN ScheduleTemplateDetail std ON s.TemplateId = std.TemplateId
    WHERE s.ScheduleId = @pScheduleId
  );
  
  SET @DateOut = @pDateFrom;
  SET @RowNumber = 0;
  SET @TimeSheetDate = dbo.GetFirstDayOfMonth(@pDateFrom);

  -- Use a temporary table for the data
  IF OBJECT_ID('tempdb..#EnumDays') IS NOT NULL DROP TABLE #EnumDays;
  CREATE TABLE #EnumDays (
    DayOrder INT,
    DateOut DATE,
    TimeSheetDate DATE
  );

  WHILE @DateOut <= @pDateTo
  BEGIN
    IF @RowNumber = @TemplateCount 
    BEGIN
      SET @RowNumber = 1;
    END
    ELSE
    BEGIN
      SET @RowNumber = @RowNumber + 1;
    END

    INSERT INTO #EnumDays (DateOut, DayOrder, TimeSheetDate)
    VALUES (@DateOut, @RowNumber, @TimeSheetDate);

    SET @DateOut = DATEADD(DAY, 1, @DateOut);
    SET @TimeSheetDate = dbo.GetFirstDayOfMonth(@DateOut);
  END;

  DELETE FROM ScheduleDetail 
  WHERE ScheduleId = @pScheduleId
    AND DateOut BETWEEN @pDateFrom AND @pDateTo;

  INSERT INTO ScheduleDetail (DateOut, TimeSheetDate, ScheduleId, AbsenceCode)
  SELECT 
    d.DateOut,
    d.TimeSheetDate,
    @pScheduleId,
    s.AbsenceCode
  FROM #EnumDays d
  CROSS JOIN (
    SELECT TemplateId FROM Schedule WHERE ScheduleId = @pScheduleId
  ) s2
  JOIN ScheduleTemplateDetail s
    ON d.DayOrder = s.DayOrder AND s.TemplateId = s2.TemplateId;
END;

CREATE FUNCTION dbo.GetFirstDayOfMonth (@pDate DATETIME)
RETURNS DATETIME
AS
BEGIN
  RETURN DATEADD(DAY, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @pDate), 0));
END;