Using a SUBQUERY for the set variable

Can you post the code in the function?

Also, why not do the CONVERT(DATE, '2017-05-09', 102), 254 in the function so you don't have to type that in each time?

because the date is not a static value there are different dates that the that the 254 days need to be applied to. Yes it pulls data from a calendar table.

create function getNextBusinessDay(@date date, @n int) returns date as
begin
  declare @nd date = dateadd(day, @n, @date);
 
  select @nd = calDate from (
  select calDate, row_number() over(order by calDate) rn from calendar c
   where c.CalDate>@date and isHoliday=0 and isWeekend=0
   ) x where rn=@n;
 
  return @nd;
end;

Select allOpen in new window

No, what I mean is why convert the date in the query each time when you can put that in the function?

alter function getNextBusinessDay(@datevc varchar(50), @n int) returns date as
begin
  declare @date date, @nd date;
 
  SET @date = (SELECT CONVERT(DATE,@datevc,102))
  SET @nd = (SELECT dateadd(day, @n, @date))

  select @nd = calDate from (
  select calDate, row_number() over(order by calDate) rn from calendar c
   where c.CalDate>@date and isHoliday=0 and isWeekend=0
   ) x where rn=@n;
 
  return @nd;

end;

Select allOpen in new window

So you only have to type select dbo.getNextBusinessDay('2017-05-09', 254) as NextDay

Can you provide information on what the other tables look like?

If you just run the query:
SELECT  IV00102.LSTCNTDT FROM IV00102 WHERE (LOCNCODE = 'CENTRAL') AND (LSTCNTDT <> CONVERT(DATETIME, '1900-01-01 00:00:00', 102))

What does it return?

Oh... Got it. the point is though i dont want to type a date i wan to have a parameter pass the date. it returns the the dates for the last count date. I have updated the query to just return distinct dates. my end goal is to use the date returned to update another column.
(SELECT DISTINCT IV00102.LSTCNTDT FROM IV00102 WHERE (LOCNCODE = 'CENTRAL') AND (LSTCNTDT <> CONVERT(DATETIME, '1900-01-01 00:00:00', 102)))
 
LSTCNTDT
2016-04-27 00:00:00.000
2016-04-28 00:00:00.000
2016-04-29 00:00:00.000
2016-05-03 00:00:00.000
2016-05-04 00:00:00.000
2016-05-05 00:00:00.000
2016-05-06 00:00:00.000
2016-05-09 00:00:00.000
2016-05-10 00:00:00.000
2016-05-11 00:00:00.000
2016-05-13 00:00:00.000
2016-05-16 00:00:00.000
2016-05-17 00:00:00.000
2016-05-18 00:00:00.000
2016-05-19 00:00:00.000
2017-05-09 00:00:00.000

Yeah you can't set the variable to an array of answers so you can only do one at a time.  If you wanted to process all that in SQL you could use something like this (not tested as I don't actually have tables but this would iterate through each result and allow you to do the update).

SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY LSTCNTDT) AS "ROW",IV00102.LSTCNTDT 
INTO #temp
FROM IV00102 
WHERE (LOCNCODE = 'CENTRAL') AND (LSTCNTDT <> CONVERT(DATETIME, '1900-01-01 00:00:00', 102))

DECLARE @x INT, @y INT
SET @x = 1
SET @y = (SELECT MAX(ROW) FROM #temp)

WHILE @x <= @y
BEGIN

	DECLARE @nextDate datetime,@lstcntdt int

	SET @lstcntdt = (SELECT LSTCNTDT FROM #temp WHERE ROW = @x)
	SET @nextDate = (select dbo.getNextBusinessDay(@lstcntdt, 254))

	--DO YOUR UPDATE QUERY HERE

	SET @x = @x + 1

END

DROP TABLE #temp

Select allOpen in new window

Dustin,
Ok, I will try your suggestion, is the function call based on your updated function or my original?

also where you have the --DO YOUR UPDATE QUERY HERE what value would I pass to that query would i pass @nextDate

Thanks I will test it

Thanks Dustin with a few mods that worked perfectly and did exactly what i wanted