Wednesday, March 7, 2012

Call Stored procedure for last 4 months then combine into final result set row

I want to call my stored proc for that last 4 months. Basically all I need to do is pass each month's first date and it will do the rest. Should I shove this into a UDF first? I'm not sure if I can do that. The struction is here behind my stored proc: http://www.webfound.net/storedproc.txt

EXEC IT_Get_Dashboard_Monthly '2006-05-03 12:03:43.910' <-- change to UDF or leave it? Then how can I loop and change each month to cover the last 4 months?

I also need to ensure all 4 values returned in each interation show up in one row in the final result set that is produced

use a simple while loop
declare @.date datetime,
@.end_date datetime
select @.date = '' -- your start date
select @.end_date = dateadd(month, -4, @.date)
while (@.date >= @.end_date)
begin
exec IT_Get_Dashboard_Monthly @.date
select @.date = dateadd(month, -1, @.date)
end

No comments:

Post a Comment