while loop in query is going under infinite loop

Question

Declare
@StartDate_in date,
@EndDate date

Set @StartDate_in =   cast('2000-01-01' as date)
Set @EndDate = cast('2010-12-31' as date)


declare @month  int
declare @year   int
declare @endyear int
declare @startdate date

set @startdate= @startdate_in

set @month = month(@StartDate_in)
set @year= year(@StartDate_in)
set @endyear= year(@EndDate)


while @year<=@endyear

Begin 

while month(@startdate)<=12 and  @year<=@endyear

begin
print (@year);
print (month(@startdate));

INSERT Into target
( YEAR,
  MONTH,
  MONTH_VAL,
 L_DT,
 U_DT,
 S_DT,
 E_DT
    )
Values
( datepart(yyyy,@StartDate) ,
 datepart(month,@StartDate) ,
substring(datename(month,@StartDate),1,3),
GETDATE(),
GETDATE(),
(GETDATE()-1),
null
)

set @StartDate = dateadd(month,1,@StartDate);

set @month=1;
end

set @year=@year+1;
print (@year);
print (@month);
end

The query although with exit condtion given in the while loop is not ending and executing infinetly

ddl of target

CREATE TABLE target(
	[TIME_ID] [int] IDENTITY(1,1) NOT NULL,
	[START_DT] [date] NULL,
	[E_DT] [date] NULL,
	[L_DT] [datetime] NULL,
	[U_DT] [datetime] NULL,
	[YEAR] varchar (5) NULL,
	[MONTH] varchar(5) NULL,
	[MONTH_VAL] [varchar](5) NULL
) ON [PRIMARY]

GO

---

Mudassar

Answer 1

I am not sure that  understood why do need a double loop 

but  can you try adding BREAK command

set @month=1;
BREAK

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

Try followings:
Declare
@StartDate_in date,
@EndDate date

Set @StartDate_in =   cast('2000-01-01' as date)
Set @EndDate = cast('2010-12-31' as date)


declare @month  int
declare @year   int
declare @endyear int
declare @startdate date
declare @innerloopyear int

set @startdate= @startdate_in

set @month = month(@StartDate_in)
set @year= year(@StartDate_in)
set @endyear= year(@EndDate)

set @innerloopyear = @year

while @year<=@endyear
Begin 

	while month(@startdate)<=12 and  @year>=@innerloopyear
	begin
		print (@year);
		print (month(@startdate));

		INSERT Into target
		( YEAR,
		  MONTH,
		  MONTH_VAL,
		 L_DT,
		 U_DT,
		 S_DT,
		 E_DT
			)
		Values
		( datepart(yyyy,@StartDate) ,
		 datepart(month,@StartDate) ,
		substring(datename(month,@StartDate),1,3),
		GETDATE(),
		GETDATE(),
		(GETDATE()-1),
		null
		)

		set @StartDate = dateadd(month,1,@StartDate);
		
		set @innerloopyear = YEAR(@startdate)
		

		set @month=1;
		

	end
print 'hello'
set @year=@year+1;
--set @StartDate = DATEADD(year,1,@StartDate)
set @innerloopyear = @year

print (@year);
print (@month);
end

I have formatted change in "Bold".

Answer 3

It didnt help it still executing 

---

Mudassar

Answer 4

Mudassar

Please try solution I posted few seconds back... that should help you.

---

Thanks, Mohan Kumar - Please mark the post as answered if it answers your question.