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:
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:
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!