Sample scripts

Use these sample scripts to create a test database, define tables, add a stored procedure, function, and trigger, and populate the tables with test data. These scripts help you better understand how to use the Debugger in dbForge Studio for SQL Server.

Create a test database

The script creates the TestScheduleDB database.

CREATE DATABASE TestScheduleDB;
GO

Create test tables and populate them with data

The script creates three tables - schedule, scheduledetail, and scheduletemplatedetail.

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 a stored procedure

The script creates the FillSchedule stored procedure. The procedure takes three input parameters - DateFrom, DateTo, and ScheduleId:

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 a function for a stored procedure

Functions, unlike procedures, return a value as a result of their execution that you can use in a statement. For example, you can call a function and get a result with the SELECT statement. However, you cannot run this statement for a procedure or use a procedure value in it. That’s why it’s required to create a function and assign its value to the TimeSheetDate variable, located in the FillSchedule procedure.

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

Note

The function must be created in the same database where you previously created the stored procedure.

Create a trigger

The script creates the trg_ScheduleDetail_Insert trigger.

CREATE TRIGGER dbo.trg_ScheduleDetail_Insert
ON dbo.ScheduleDetail
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;    DECLARE @tsdate DATE;
    SELECT @tsdate = TimeSheetDate FROM inserted;    IF @tsdate IS NULL
        RAISERROR('TimeSheetDate cannot be NULL.', 16, 1);
    ELSE
        INSERT INTO dbo.ScheduleDetail (ScheduleId, DateOut, TimeSheetDate, AbsenceCode)
        SELECT 
            ScheduleId, 
            DateOut, 
            TimeSheetDate, 
            ISNULL(AbsenceCode, 'sicklist')
        FROM inserted;
END;
GO

Create a stored function

The script creates the GetAbsenceCountByCode stored function.

CREATE FUNCTION dbo.GetAbsenceCountByCode ( @AbsenceCode VARCHAR(25) ) 
RETURNS INT 
AS BEGIN 
DECLARE @ret INT = 0; 
DECLARE @counter INT = 0; 
DECLARE @total INT;   
DECLARE @temp TABLE (rownum INT IDENTITY(1,1), dummy INT);   
INSERT INTO @temp(dummy) SELECT 1 FROM dbo.ScheduleDetail WHERE AbsenceCode = @AbsenceCode;   
SELECT @total = COUNT(*) FROM @temp;  
WHILE @counter < @total 
BEGIN 
SET @counter = @counter + 1; 
SET @ret = @ret + 1; 
END   
RETURN @ret; 
END;