Avatar of Jeanette Durham
Jeanette Durham
Flag 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]
/****** 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]
/****** 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!


SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon

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

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

Open in new window


Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott Pletcher

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.