LikeableBias

-- Declare # of years you want dates for, the month, the day, and the occurrence of that day within the month declare @years tinyint = 15; declare @month_name varchar(10) = 'November'; declare @day_name varchar(10) = 'Friday'; declare @week_num tinyint = 4; -- Build the calendar WITH calendar AS (   SELECT CAST(getdate() as date) AS [date]   UNION ALL   SELECT DATEADD(dd, 1, [date])   FROM calendar   WHERE DATEADD(dd, 1, [date]) <= dateadd(year,@years,getdate()) ) -- Get the month and days that apply , month_days as ( SELECT [date]   ,row_number() over (partition by year([date]),month([date]) order by date asc) as [week_num] FROM calendar where 1=1 and DATENAME(weekday,[date]) = @day_name and datename(month,[date]) = @month_name ) -- Get the dates where the occurrence matches select [date] from month_days where week_num = @week_num OPTION (MAXRECURSION 0) --Unlimited recursion; limit the number of years in parameter at the top ;

-- Create a test DB CREATE DATABASE [testdb]; go -- Switch the DB context USE [testdb]; go -- Create a basic logging table CREATE TABLE dbo.sp_log ( id BIGINT NOT NULL IDENTITY(1, 1), proc_name NVARCHAR(50) NOT NULL, execution_time DATETIME NOT NULL, params NVARCHAR(max) NULL ); go -- Create a sample target SP that accepts a couple of parameters CREATE PROCEDURE [dbo].[Test_proc] (@param1 VARCHAR(50), @param2 VARCHAR(50)) AS BEGIN -- Whatever your other stored procedures do goes here. -- Just selecting the params back out as an example. SELECT @param1, @param2; END; go -- Create the helper proc to handle parsing of sp and its param values CREATE PROCEDURE dbo.Sp_helper (@args NVARCHAR(max)) AS BEGIN -- Figure out where the SP name ends and the params begin DECLARE @paramstartpos INT = Charindex('||', @args,