'Skipping' occurs during sql while statement using rowcount

Question

I'm working on a stored procedure to locate incorrectly formatted Addr2 lines and either auto-correct them, or move them to an exceptions table. I've been successful, to a degree-- the sproc 'skips' the way a record does, and has to be run two or three times before all rows are processed. This is obviously not what I want. After paring it down, I have come to the core issue:

----previous code to establish temp table #Addr2Chck, which inserts questionable order records and assigns row number------

set @NumberRecords = @@ROWCOUNT
set @rowcount=1

WHILE @rowcount<= @NumberRecords
BEGIN

SELECT @Cusid= Cusid,
@OrderNum= Ordernum,
@StndAddr2= Addr2,
from @Addr2Chck
where ROWID=@ROWCOUNT

update corp_orders
set addr2=@Stnd2
from (SELECT rowed, t.OrderNum from #Addr2Chck c left join corp_orders t   
on c.ordernum=t.ordernum) as j
where corp_orders.ordernum=@ordernum and corp_orders.cusid=@cusid and    j.rowid=@rowcount

SET @ROWCOUNT= @ROWCOUNT + 1
END

Obviously there are if-else statements that would take place within this loop, but if I don't get the simple version to work, I'm going to have surds all over the place. I'm sure I'm missing something glaringly obvious and have developed mind-blindness.

Here are some of the sources I've used for reference: http://www.java2s.com/Code/SQLServer/Transact-SQL/UsingROWCOUNT.htm Using row count from a temporary table in a while loop SQL Server 2008

as well as the Microsoft SQL AdventureWorks textbook.

I need to do this record by record instead of batching, which I'm sure is also a large part of the problem.

Thank you for your time!

ETA: This link was given to me at the beginning of the project as a reason why a cursor should be avoided: http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx

Answer 1

First: full agreement with all the comments - loops in SQL are almost always avoidable. Lets ignore that and answer the question for now.

There were also typos in you code - I assume most are accidental. The one that concerns me most is that you have BOTH FROM @Addr2Chck and FROM #Addr2Chck. Both of these are possible incarnations of a temporary table. Using BOTH could be the source of your problem.

My answer assumes a TABLE VAR, something like

DECLARE @Addr2Chck TABLE (RowID INT PRIMARY KEY , CusID INT, OrderNum INT, Addr2 VARCHAR(99))

Yes, note the KEY on RowID. One possible cause of your error is that you are ending up with DUPLICATES on RowID earlier (in the code you omitted).

My suggestion: Do not use @@ROWCOUNT or independent incremented variable.

Try something like this variation. Still an evil loop, but it avoids the assumption that RowID is perfectly distributed across the row count.

DECLARE @RowIDNow INT
DECLARE @CusID INT, @OrderNum INT, @StndAddr2 VARCHAR(MAX)
-- test data
insert into @Addr2Chck values (1, 2,3,'norwalk')
insert into @Addr2Chck values (3, 4,5,'westchester')

SET NOCOUNT ON 
WHILE EXISTS (SELECT * FROM @Addr2Chck )  
BEGIN

SET @RowIDNow = (SELECT MIN( RowID ) FROM @Addr2Chck )
print CONVERT(VARCHAR(33),GETDATE(), 121) + ' # ' + STR(@RowIDNow) -- purely for illustration

SELECT @Cusid= Cusid,
   @OrderNum= Ordernum,
   @StndAddr2= Addr2
from @Addr2Chck
where ROWID=@RowIDNow

-- note: changed #Addr2Chck to @Addr2Chck
update corp_orders
   set addr2=@Stnd2
from (SELECT rowed, t.OrderNum from @Addr2Chck c left join corp_orders t   
   on c.ordernum=t.ordernum) as j
where corp_orders.ordernum=@ordernum and corp_orders.cusid=@cusid and       j.rowid= @RowIDNow

DELETE FROM @Addr2Chck WHERE RowID = @RowIDNow;
END -- of the evil look