SQL Coding stuck in infinite loop

I seem to be struggling with the SQL code that I created and needs a few pointers on where I am going wrong. It seems to be caught in an infinite loop. I know it will be obvious once pointed out but its still a learning curve at the moment.

 DECLARE @TOTAL as int
DECLARE @CUSTOMER varchar(8)
DECLARE @PARTNO varchar(20)
DECLARE @CASE_DISCOUNT numeric(18, 2)
DECLARE @OVERRIDE_DISCOUNT numeric(18, 2)


DECLARE @COUNTER as int
SET @COUNTER = 1

SELECT        dbo.MBG110.CUSTOMER, dbo.MBG110.CUSTNAME, dbo.MBG130.SORDNO, dbo.MBG160.CINVOICE, dbo.MBG130.CUSTORDATE, dbo.MBG130.CUSTREQDTE_SOR, dbo.MBG160.CINVDATE,
                         dbo.MBG160.PARTNO_DS4, dbo.MBB010.DESCRIPTION, dbo.MBG160.DESQTY, dbo.MBB010.WEIGHT_ITM, dbo.MBG160.DESQTY * dbo.MBB010.WEIGHT_ITM AS ORDER_WEIGHT,
                         dbo.MBG130.SIINVVAL_SOR
                                     INTO #TempSales
FROM            dbo.MBG110 RIGHT OUTER JOIN
                         dbo.MBG130 ON dbo.MBG110.CUSTOMERLONG = dbo.MBG130.CUSTOMER_SOR LEFT OUTER JOIN
                         dbo.MBG160 ON dbo.MBG130.SORDNO = dbo.MBG160.SORDNO_DES LEFT OUTER JOIN
                         dbo.MBB010 ON dbo.MBG160.PARTNO_DS4 = dbo.MBB010.PARTNO

ALTER TABLE #TempSales ADD Case_Discount numeric(18, 2)
ALTER TABLE #TempSales ADD Override_Discount numeric(18, 2)
ALTER TABLE #TempSales ADD RowId int identity(1,1)

SELECT @TOTAL = COUNT(CUSTOMER) FROM #TempSales

SELECT        dbo.MBN510.PROMOCODE_N51, dbo.MBN510.CUSTOMER_N51, dbo.MBN510.EFFDATE_N51, dbo.MBN510.TERMDATE_N51, dbo.MBN520.PARTNO_N52, dbo.MBN520.UOM_REB,
                         dbo.MBN520.REBVALPERC
                                     INTO #TempDiscounts
FROM            dbo.MBN510 LEFT OUTER JOIN
                         dbo.MBN520 ON dbo.MBN510.PROMOCODE_N51 = dbo.MBN520.PROMOCODE_N52

WHILE(@COUNTER <= @TOTAL)
BEGIN

SELECT @CUSTOMER = CUSTOMER, @PARTNO = PARTNO_DS4 FROM #TempSales WHERE RowId = @COUNTER

SELECT @CASE_DISCOUNT = REBVALPERC FROM #TempDiscounts WHERE CUSTOMER_N51 = @CUSTOMER AND PARTNO_N52 = @PARTNO AND UOM_REB = 'CASE'

SELECT @OVERRIDE_DISCOUNT = REBVALPERC FROM #TempDiscounts WHERE CUSTOMER_N51 = @CUSTOMER AND PARTNO_N52 = @PARTNO AND UOM_REB <> 'CASE'
UPDATE #TempSales SET Case_Discount = @CASE_DISCOUNT
UPDATE #TempSales SET Override_Discount = @OVERRIDE_DISCOUNT
END
SET @COUNTER = @COUNTER + 1

sqlError.jpg
LVL 1
David CIT Projects ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team LeadCommented:
try:

SET @COUNTER = @COUNTER + 1
END

Open in new window


instead of:

END
SET @COUNTER = @COUNTER + 1

Open in new window


?
ste5anSenior DeveloperCommented:
Some comments:
- Use a cursor instead of your loop.
- Use a primary key instead of your RowID construct.
- The SELECT statements for @CASE_DISCOUNT and @OVERRIDE_DISCOUNT can return abritrary values, if you don't use a primary/candidate key in your WHERE condition and without TOP (1) / ORDER BY.

Finally:
You update always the entire sales temporary table. Thus you don't need a loop or cursor at all.

But when this is no correct, then you can also calculate all the necessary values @CASE_DISCOUNT and @OVERRIDE_DISCOUNT when creating your temporary discount table. Thus you should be able to use single UPDATE using both temporary tables instead of a loop/cursor.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David CIT Projects ManagerAuthor Commented:
lots of points to work on, thanks
David CIT Projects ManagerAuthor Commented:
Thanks both, great help and tips
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.