Return smalldatetime value from scalar function SELECT query

Question

I'm looking to create a scalar function in SQL Server (2017) that leverages a calendar table I built awhile back in order to calculate and return a date a given number of business days forward in time from a given date. I have been struggling with how to pass the SMALLDATETIME return value back appropriately. To give some idea what I'm attempting:

CREATE FUNCTION dbo.AddBusDaysToDate
(
   @startDate SMALLDATETIME,
   @numBusDays INT,
)
RETURNS SMALLDATETIME
AS
BEGIN
   DECLARE @rs SMALLDATETIME;

   SELECT @rs = TOP(1) dt
   FROM (
        SELECT TOP(@numBusDays) dt
        FROM dbo.OurCalendar
        WHERE isWeekday = 1
        AND isHoliday = 0
        AND dt >= @startDate
        ORDER BY dt ASC
    ) as ID
    ORDER BY dt DESC

    RETURN @rs
END

dt is a SMALLDATETIME data type on our calendar table.

The query itself runs as intended when values plugged in for the variables, but I was trying to repurpose a similar function that calculated the difference in business days between two points on the calendar, with a different data type. So I'm unsure if I'm pulling in a row to the @rs instead of the individual value, or how to separate/isolate that specific 'cell' from the SELECT query result. I expect I'm probably missing something very simple.

Any help or a point in the right direction would be very well appreciated.

Answer 1

I was able to resolve with the following:

CREATE FUNCTION dbo.AddBusDaysToDate
(
    @startDate SMALLDATETIME,
    @numBusDays INT,
)
RETURNS SMALLDATETIME
AS
BEGIN
   DECLARE @rs SMALLDATETIME;
   DECLARE @workdayModifier INT;

   IF EXISTS (
      SELECT dt FROM dbo.OurCalendar 
      WHERE dt = @startDate 
      AND isWeekday = 1 
      AND isHoliday = 0
      )
   SET @workdayModifier = 1
   ELSE
   SET @workdayModifier = 0


   SELECT TOP(1) @rs = dt
       FROM (
           SELECT TOP(@numBusDays + @workdayModifier) dt
           FROM dbo.OurCalendar
           WHERE isWeekday = 1
           AND isHoliday = 0
           AND dt >= @startDate
           ORDER BY dt ASC
       ) as ID
       ORDER BY dt DESC
   
   RETURN @rs    

END