locked
while loop in query is going under infinite loop RRS feed

  • 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



    • Edited by MLTC Wednesday, January 29, 2014 9:41 AM hide business key
    Wednesday, January 29, 2014 9:34 AM

Answers

  • 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".


    • Edited by Mohan Kumar - CSA Wednesday, January 29, 2014 9:58 AM
    • Marked as answer by MLTC Wednesday, January 29, 2014 10:10 AM
    Wednesday, January 29, 2014 9:56 AM
  • 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.

    • Marked as answer by MLTC Wednesday, January 29, 2014 10:09 AM
    Wednesday, January 29, 2014 9:59 AM

All replies

  • 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

    Wednesday, January 29, 2014 9:51 AM
    Answerer
  • 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".


    • Edited by Mohan Kumar - CSA Wednesday, January 29, 2014 9:58 AM
    • Marked as answer by MLTC Wednesday, January 29, 2014 10:10 AM
    Wednesday, January 29, 2014 9:56 AM
  • It didnt help it still executing 

    Mudassar

    Wednesday, January 29, 2014 9:56 AM
  • 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.

    • Marked as answer by MLTC Wednesday, January 29, 2014 10:09 AM
    Wednesday, January 29, 2014 9:59 AM