I have a stored procedure that is used to sync dates between two databases. It looks at the project start date in one database and the production date for a specific component in the other database. If that production date is later than the project start date, it syncs the two dates.
The stored procedure is run at midnight each night. Component production is set at 2:30pm each day. So, for example, at 2:30pm on July 1st, the production for July 2nd is set. Thus it doesn't do any good to have the stored procedure set production dates to July 1st or July 2nd and instead should use July 3rd as the next available start date.
This issue is addressed by checking if the intended start date is at least one day in the future and, if not, changing the intended start date to be such.
What I'm not entirely sure how to go about doing is checking to ensure that the new intended start date isn't a Saturday, Sunday or Holiday.
Checking if the date is a Saturday or Sunday is easy enough using DATENAME(dw, @STARTDATE) and then adding 1 or 2 days to the date accordingly.
What I'm not entirely sure about is the proper way to continue adding days to the date until it's neither a Saturday no Sunday and also doesn't appear in the Holidays table.
What would be the proper structure of a loop to ensure the date is neither a weekend nor a holiday?