Link to home
Start Free TrialLog in
Avatar of Jeanette Durham
Jeanette DurhamFlag for United States of America

asked on

SQL Server 2014 - infinite while loop with table and incrementing integer field

I have an infinite while loop that is blowing my mind. I'm using sql server 2014. The loop will count correctly all the way up to one number below the max count and then just retrieve the same record from the table over and over again, even though at that point the record shouldn't even exist. I cut down my sproc to the most basic code and also pared down a sample table so y'all can easily reproduce this.


Here is the code to create the example table:

USE [TheTexting]
GO /****** Object:  Table [dbo].[ListsMainOut]    Script Date: 9/29/2021 9:26:17 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[ListsTest](    [ListId] [bigint] NOT NULL,    [TextsMaxDaily] [int] NULL CONSTRAINT [DF_ListsTest_TextsMaxDaily]  DEFAULT ((200)),    [NumSentToday] [int] NOT NULL CONSTRAINT [DF_ListsTest_NumSentToday]  DEFAULT ((0)),    [LMOId] [bigint] IDENTITY(1,1) NOT NULL,    [LoopInc] [int] NULL,  CONSTRAINT [PK_ListsTests] PRIMARY KEY CLUSTERED  (    [ListId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object:  Index [IX_LMOId]    Script Date: 9/29/2021 9:27:07 AM ******/ CREATE NONCLUSTERED INDEX [IX_ListsTest_LMOId] ON [dbo].[ListsTest] (    [LMOId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO insert into dbo.ListsTest (ListId,TextsMaxDaily) Values (1,10);

Open in new window


Here is my stored procedure that is having an issue:

USE [TheTexting]
GO /****** Object:  StoredProcedure [dbo].[Lists_Load]    Script Date: 9/27/2021 1:45:42 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================================================================================= -- Author:      Jeanette Durham -- Create date: 9/29/2021 -- Last update: 9/29/2021 -- Description: Demonstrates the infinite while loop I cannot figure out /*       exec dbo.[ListsMainOut_RunTest]       select * from dbo.ListsTest       update dbo.ListsTest set NumSentToday=0 */ -- ============================================================================================================= alter PROCEDURE [dbo].[ListsMainOut_RunTest] AS BEGIN    set nocount on    update dbo.ListsTest set LoopInc=0;  -- field that will let us rotate between these    declare @bGo bit = 1, @iNumSent int, @iNumMax int, @LMOId bigint, @iLoopInc int;    while @bGo = 1    begin       -- to be good, we need the right day and we can only send out so many a day       -- they should also be throttled so far apart       select top 1 @iNumSent=[NumSentToday],@iNumMax=[TextsMaxDaily], @iLoopInc=[LoopInc], @LMOId=LMOId          from dbo.ListsTest where NumSentToday < TextsMaxDaily          order by LoopInc, LMOid;       if @LMOId is null begin          print '@LMOId is null'          break;  -- if there are no records we are done       end       print concat(@LMOId,': ',@iNumSent,'/',@iNumMax,', ',@iLoopInc);       waitfor delay '00:00:01';       -- mark that the text was sent and when so we can move on to the next one       update dbo.ListsTest set NumSentToday=@iNumSent + 1, LoopInc=@iLoopInc + 1          where [LMOId]=@LMOId;    end END

Open in new window

So when I run this it counts from 0-9 and then never reaches the threshold of 10, and just keeps pulling the 9th record over and over.


Here is my output:

1: 0/10, 0
1: 1/10, 1 1: 2/10, 2 1: 3/10, 3 1: 4/10, 4 1: 5/10, 5 1: 6/10, 6 1: 7/10, 7 1: 8/10, 8 1: 9/10, 9 1: 9/10, 9 1: 9/10, 9 1: 9/10, 9 1: 9/10, 9 1: 9/10, 9 1: 9/10, 9 1: 9/10, 9 1: 9/10, 9 1: 9/10, 9 1: 9/10, 9 1: 9/10, 9 Query was cancelled by user.

Open in new window

Does anyone know why this isn't working and what I can do to make it work? I've even tried putting the contents of the loop inside a transaction in case the record isn't being updated between runs - although I'm of the understanding that it commits each query as it runs it if you don't. 


This blows my mind I can't get the most basic while loop to work. Thank you very much for your help!

Jeanette

Avatar of ste5an
ste5an
Flag of Germany image

You don't change the control variable @bGo in your loops body. Thus it never terminates.
Avatar of Jeanette Durham

ASKER

It's supposed to exit the while loop the first time the select statement retrieves no record.

That's the intent of this section:
if @LMOId is null begin
         print '@LMOId is null'
         break;  -- if there are no records we are done
      end

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
A SELECT that assign variables but doesn't find a row to read does not reset the variables to NULL (it's logical to think it would, but in fact it does not).  Thus, the @vars still contain what they contained before the SELECT was run.

Thus, change your code to add a NULL setting for the control variable:
      -- to be good, we need the right day and we can only send out so many a day       
      -- they should also be throttled so far apart       
      set @LMOId = NULL; --<<-- add this
      select top (1) @iNumSent=[NumSentToday],@iNumMax=[TextsMaxDaily], @iLoopInc=[LoopInc], @LMOId=LMOId          
      from dbo.ListsTest 
      where NumSentToday < TextsMaxDaily          
      order by LoopInc, LMOid;       
       if @LMOId is null begin        

Open in new window