While Loop Stored Procedure

Question

Post reply

While Loop Stored Procedure

Daniel.Bayliss

Old Hand

Points: 303

November 6, 2018 at 8:59 am

#382262

Hi,

Can help me with an issue I am facing. I need to create a single stored procedure that uses the Start and end date of each month - executing a Script for each year/ month in chronoligical order.

For Example I can create a reference table like the below, then I need the Stored proc to reference the table then run year 2017 month 1 input the Monthstart and monthend dates as parameters into the Stored proc - process then move onto Year 2017 Month 2 input the Monthstart and month end as parameters process etc.....until the last Year/ month has been reached within the reference table.
Could someone help with best practice appraoches to solving this problem

Many thanks in advance.

Year	Month	MonthStart	MonthEnd
2017	1	01/01/2017 00:00	31/01/2017 00:00
2017	2	01/02/2017 00:00	28/02/2017 00:00
2017	3	01/03/2017 00:00	31/03/2017 00:00
2017	4	01/04/2017 00:00	30/04/2017 00:00
2017	5	01/05/2017 00:00	31/05/2017 00:00

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63500 More actions · Answer 1

Can you post the definition of the stored procedure? I'm just wondering if you really need to do this with a cursor. Most of the time, "cursor" is a dirty word around here.

debasis.yours Hall of Fame Points: 3457 More actions · Answer 2

Hi,

Can the following approach help?

CREATE PROCEDURE ExecuteBatch AS BEGIN DECLARE @CurrentMonth INT DECLARE @MaxMonth INT DECLARE @Year INT DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME SELECT @MaxMonth = MAX(MonthValue) FROM ReferenceTable -- MonthValue column should be like 201701,201702 (by combining year and month) SELECT @CurrentMonth = MIN(MonthValue) FROM ReferenceTable WHILE(@CurrentMonth<@MaxMonth) BEGIN SELECT @StartDate = StartDate FROM ReferenceTable SELECT @EndDate = EndDate FROM ReferenceTable -- Call the process with @StartDate and @EndDate as parameter SET @CurrentMonth = @CurrentMonth + 1 IF(CAST(@CurrentMonth AS VARCHAR) LIKE '%13') BEGIN SET @Year = CAST(SUBSTRING(CAST(@CurrentMonth AS VARCHAR),1,4) AS INT) SET @CurrentMonth = CAST(CAST(@Year +01 AS VARCHAR) + '01' AS INT) END END END

Thanks.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

Daniel.Bayliss - Tuesday, November 6, 2018 8:59 AM
Hi,
Can help me with an issue I am facing. I need to create a single stored procedure that uses the Start and end date of each month - executing a Script for each year/ month in chronoligical order.
For Example I can create a reference table like the below, then I need the Stored proc to reference the table then run year 2017 month 1 input the Monthstart and monthend dates as parameters into the Stored proc - process then move onto Year 2017 Month 2 input the Monthstart and month end as parameters process etc.....until the last Year/ month has been reached within the reference table.
Could someone help with best practice appraoches to solving this problem
Many thanks in advance.

Year Month MonthStart MonthEnd
2017 1 01/01/2017 00:00 31/01/2017 00:00
2017 2 01/02/2017 00:00 28/02/2017 00:00
2017 3 01/03/2017 00:00 31/03/2017 00:00
2017 4 01/04/2017 00:00 30/04/2017 00:00
2017 5 01/05/2017 00:00 31/05/2017 00:00

If you ever have rows with time involved, the use of "MonthEnd" as you have it will miss all but the first instant of the last day of the month. I recommend that, instead of "MonthEnd", that you use "NextMonthStart" and make your criteria follow the general form of >= MonthStart and < "NextMonthStart".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Daniel.Bayliss Old Hand Points: 303 More actions · Answer 4

Hi
I tired the approach suggest, but the output defaults to the latest month only. I'm not familiar with the while loop funcationality - can you please explain how I can adapt the below to account for 8 months I have in the example?
Thank you very much for your help

DROP TABLE [dbo].[Reference_Month_Start_End]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Reference_Month_Start_End](

[Reference] [int] IDENTITY(1,1) NOT NULL,

[Year] [int] NOT NULL,

[Month] [int] NOT NULL,

[MonthStart] [datetime] NULL,

[MonthEnd] [datetime] NULL,

[MonthValue] [varchar](10) NULL

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[Reference_Month_Start_End] ON

GO

INSERT [dbo].[Reference_Month_Start_End] ([Reference], [Year], [Month], [MonthStart], [MonthEnd], [MonthValue]) VALUES (1, 2018, 4, CAST(N'2018-04-01T00:00:00.000' AS DateTime), CAST(N'2018-04-30T00:00:00.000' AS DateTime), N'201804')

GO

INSERT [dbo].[Reference_Month_Start_End] ([Reference], [Year], [Month], [MonthStart], [MonthEnd], [MonthValue]) VALUES (2, 2018, 5, CAST(N'2018-05-01T00:00:00.000' AS DateTime), CAST(N'2018-05-31T00:00:00.000' AS DateTime), N'201805')

GO

INSERT [dbo].[Reference_Month_Start_End] ([Reference], [Year], [Month], [MonthStart], [MonthEnd], [MonthValue]) VALUES (3, 2018, 6, CAST(N'2018-06-01T00:00:00.000' AS DateTime), CAST(N'2018-06-30T00:00:00.000' AS DateTime), N'201806')

GO

INSERT [dbo].[Reference_Month_Start_End] ([Reference], [Year], [Month], [MonthStart], [MonthEnd], [MonthValue]) VALUES (4, 2018, 7, CAST(N'2018-07-01T00:00:00.000' AS DateTime), CAST(N'2018-07-31T00:00:00.000' AS DateTime), N'201807')

GO

INSERT [dbo].[Reference_Month_Start_End] ([Reference], [Year], [Month], [MonthStart], [MonthEnd], [MonthValue]) VALUES (5, 2018, 8, CAST(N'2018-08-01T00:00:00.000' AS DateTime), CAST(N'2018-08-31T00:00:00.000' AS DateTime), N'201808')

GO

INSERT [dbo].[Reference_Month_Start_End] ([Reference], [Year], [Month], [MonthStart], [MonthEnd], [MonthValue]) VALUES (6, 2018, 9, CAST(N'2018-09-01T00:00:00.000' AS DateTime), CAST(N'2018-09-30T00:00:00.000' AS DateTime), N'201809')

GO

INSERT [dbo].[Reference_Month_Start_End] ([Reference], [Year], [Month], [MonthStart], [MonthEnd], [MonthValue]) VALUES (7, 2018, 10, CAST(N'2018-10-01T00:00:00.000' AS DateTime), CAST(N'2018-10-31T00:00:00.000' AS DateTime), N'201810')

GO

INSERT [dbo].[Reference_Month_Start_End] ([Reference], [Year], [Month], [MonthStart], [MonthEnd], [MonthValue]) VALUES (8, 2018, 11, CAST(N'2018-11-01T00:00:00.000' AS DateTime), CAST(N'2018-11-30T00:00:00.000' AS DateTime), N'201811')

GO

SET IDENTITY_INSERT [dbo].[Reference_Month_Start_End] OFF

GO

/*

Need to understand what is going on here

*/

BEGIN

DECLARE @CurrentMonth INT

DECLARE @MaxMonth INT

DECLARE @Year INT

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SELECT @MaxMonth = MAX(MonthValue) FROM Reference_Month_Start_End -- MonthValue column should be like 201701,201702 (by combining year and month)

SELECT @CurrentMonth = MIN(MonthValue) FROM Reference_Month_Start_End

WHILE(@CurrentMonth<@MaxMonth)

BEGIN

PRINT @MaxMonth;

SELECT @StartDate = MonthStart FROM Reference_Month_Start_End

SELECT @EndDate = MonthEnd FROM Reference_Month_Start_End

PRINT @StartDate;PRINT @EndDate;

--Insert Stored Proc here Inc @parameters

SET @CurrentMonth = @CurrentMonth + 1

IF(CAST(@CurrentMonth AS VARCHAR) LIKE '%13')

BEGIN

SET @CurrentMonth = CAST(SUBSTRING(CAST(@CurrentMonth AS VARCHAR),1,4) AS INT)

SET @Year = CAST(CAST(@Year + 01 AS VARCHAR) + '01' AS INT)

END

debasis.yours Hall of Fame Points: 3457 More actions · Answer 5

Hi Daniel,

I am sorry, I missed a crucial condition while fetching the StartDate and EndDate in the loop. Please change the following lines with the where clause:
WHILE(@CurrentMonth<@MaxMonth) BEGIN PRINT @MaxMonth; SELECT @StartDate = MonthStart FROM Reference_Month_Start_End WHERE [MonthValue] = @CurrentMonth SELECT @EndDate = MonthEnd FROM Reference_Month_Start_End WHERE [MonthValue] = @CurrentMonth

Please let me know if this solves.

Daniel.Bayliss Old Hand Points: 303 More actions · Answer 6

This is perfect - Thank you very much for your help.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Daniel.Bayliss - Tuesday, November 6, 2018 8:59 AM
Hi,
Can help me with an issue I am facing. I need to create a single stored procedure that uses the Start and end date of each month - executing a Script for each year/ month in chronoligical order.
For Example I can create a reference table like the below, then I need the Stored proc to reference the table then run year 2017 month 1 input the Monthstart and monthend dates as parameters into the Stored proc - process then move onto Year 2017 Month 2 input the Monthstart and month end as parameters process etc.....until the last Year/ month has been reached within the reference table.
Could someone help with best practice appraoches to solving this problem
Many thanks in advance.

Year Month MonthStart MonthEnd
2017 1 01/01/2017 00:00 31/01/2017 00:00
2017 2 01/02/2017 00:00 28/02/2017 00:00
2017 3 01/03/2017 00:00 31/03/2017 00:00
2017 4 01/04/2017 00:00 30/04/2017 00:00
2017 5 01/05/2017 00:00 31/05/2017 00:00

How many result sets do you want the stored procedure to return? One per month?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

debasis.yours - Tuesday, November 6, 2018 9:19 AM
Hi,
Can the following approach help?
CREATE PROCEDURE ExecuteBatch AS BEGIN DECLARE @CurrentMonth INT DECLARE @MaxMonth INT DECLARE @Year INT DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME SELECT @MaxMonth = MAX(MonthValue) FROM ReferenceTable -- MonthValue column should be like 201701,201702 (by combining year and month) SELECT @CurrentMonth = MIN(MonthValue) FROM ReferenceTable WHILE(@CurrentMonth<@MaxMonth) BEGIN SELECT @StartDate = StartDate FROM ReferenceTable SELECT @EndDate = EndDate FROM ReferenceTable -- Call the process with @StartDate and @EndDate as parameter SET @CurrentMonth = @CurrentMonth + 1 IF(CAST(@CurrentMonth AS VARCHAR) LIKE '%13') BEGIN SET @Year = CAST(SUBSTRING(CAST(@CurrentMonth AS VARCHAR),1,4) AS INT) SET @CurrentMonth = CAST(CAST(@Year +01 AS VARCHAR) + '01' AS INT) END END END
Thanks.

What I'd like to know is what is the process that you're calling once for each month?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)