March 30, 2016 at 5:13 pm
I often execute stored procedures which pull data into an organized weeks starting on Monday. This is done by naming @WeekStartDate & @WeekEndDate as parameters. This structure is needed due to how certain metrics are set up and communicated. Once I have created my SP I then have to Execute numerous # of weeks which isn't that difficult but I would like to automate if possible.
In other words instead of Typing out the following: (possibly over 100 times)
Execute SP_Name 3/7/16, 3/13/16
Execute SP_Name 3/14/16, 3/20/16
Execute SP_Name 3/21/16, 3/27/16
I would like to build some type of TVP, Loop, ect that could do it all at once. Please let me know your thoughts and if other details/info should be provided for clear context.
-L
March 31, 2016 at 1:08 am
Not tested but is something like this what you are after:
DECLARE @WKSTARTDATE DATETIME = '2016-01-04'; -- for example first Monday of Jan 2016
DECLARE @NUMBEROFWKS INT = 10; -- number of weeks to project forward
DECLARE @CURRENTWK INT = 0; -- loop counter
WHILE @CURRENTWK <= @NUMBEROFWKS
BEGIN
DECLARE @STARTDATE DATETIME = DATEADD (wk , @CURRENTWK , @WKSTARTDATE ) -- get the start date of the week
DECLARE @ENDDATE DATETIME = DATEADD (d , 6 , @STARTDATE ) -- work out the end date of the week
SELECT CONVERT(VARCHAR,@STARTDATE,103) + ' end date of week is ' + CONVERT(VARCHAR,@ENDDATE,103) -- display purposes only
SET @CURRENTWK = @CURRENTWK+1 -- increment the loop
END
April 1, 2016 at 11:22 am
I haven't used the loop function you are suggesting but i assume it would work as follows: I set the number of weeks that I want, build out week start date & week end date parameters and then it would run a stored procedure several times (same as number of weeks) building on itself and at the same time it would change my parameters to update to the next week, then the week after that and so on?
That is where I am trying to get to, please let me know if that sounds like the functionality of what you are suggesting.
-L
April 1, 2016 at 12:37 pm
You would execute your stored procedure inside the WHILE loop.
WHILE @CurrDate<@EndDate
BEGIN
EXEC myStoredProc @Param1, @CurrDate;
SET @CurrDate = DATEADD(wk,1,@CurrDate);
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply