If you see the below stored procedure it has 2019-01-01,2019-12-31 as input.
What I would like to do is, I need to pass 2019-01-01,2019-01-31 and then 2019-02-01,2019-02-28 and same till December.
Can anyone please help in how to pass date time by month dynamically.
Exec storedprcedurename '2019-01-01','2019-12-31'
You can make use of while loop and date add to execute it. Also, you may need to set a max date so that it does not go into infinite loop.
Declare @datemin date = '2019-01-01', @maxdate date = '2019-12-31'
While @datemin <= @maxdate
Begin
Declare @dateend date = dateadd(day, -1, dateadd(month,1,@datemin ))
Exec storedprcedurename @datemin , @dateend
set @datemin = dateadd(month, 1, @datemin)
end
I would probably do it with dynamic SQL. Normally, I wouldn't recommend dynamic SQL without using the @params parameter to sp_executesql, but in this case, you're controlling the input values yourself.
So first we need to define a dataset with the dates to use to pass to the stored procedure. It's enough to get the first day of each month in the result set - we can use EOMONTH-function to get the last day of each month.
I'm doing it using a CTE below.
--Define startdate
DECLARE @startdate date='2019-01-01';
--Number of months
DECLARE @numMonths int=12;
--variable to store some dynamic SQL in
DECLARE @dynSQL nvarchar(max)=N'';
--CTE to get the dates
WITH tens AS(
SELECT n FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(n)
), thousands AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 as n
FROM tens t0
CROSS JOIN tens t1
CROSS JOIN tens t2
CROSS JOIN tens t3
), dates AS(
SELECT TOP(@numMonths) DATEADD(month,n,@startDate) as startDate
FROM thousands
ORDER BY n
)
--And now we construct the dynamic SQL statement to call the proc based on the CTE dates
SELECT
@dynSQL = @dynSQL +
N'exec storedprocedurename ''' +
cast(startDate as nvarchar(50)) +
N''',''' +
cast(EOMONTH(startDate) as nvarchar(50)) +
N''';
'
FROM dates ORDER BY startDate;
--Make sure the dynamic SQL string looks correct Before executing
PRINT @dynSQL;
--When you are sure you got it right, comment the PRINT statement and uncomment below
--exec sp_executesql @dynSQL;
You could do it with a cursor, and the code would probably be a bit easier to read. But I'm brought up to shy away from cursors...
