troubleshooting Question

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

Avatar of Jeanette Durham
Jeanette DurhamFlag for United States of America asked on
Microsoft SQL ServerSQL
4 Comments2 Solutions11 ViewsLast Modified:

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);


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

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.

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

ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros