while loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

while loop

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-04-18 : 00:12:09

hi all, ive no idea what's wrong with my while loop nested in IF .. it only work correctly when i remove the while from IF :-


IF @Picktype='FI'
BEGIN
	-- Insert data into @Stage to play around		
	DECLARE	@Stage TABLE (RecID INT IDENTITY(1, 1), ItemStorageID VARCHAR(12), Qty MONEY, RecvDate DATETIME,BB char(1))
	
	WHILE (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) < @WantedValue AND @@ROWCOUNT > 0
		INSERT		@Stage (ItemStorageID, Qty, RecvDate, BB)
		SELECT TOP 1	t1.ItemStorageID,
				t1.Qty,
				t1.RecvDate,
				t1.BB
		FROM		#DataList AS t1
		LEFT JOIN	@Stage AS s ON s.ItemStorageID = t1.ItemStorageID
		WHERE		s.ItemStorageID IS NULL
		ORDER BY	t1.RecvDate,
				t1.Qty DESC
	
	IF (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) >= @WantedValue
		SELECT	recID,
			ItemStorageID,
			Qty,
			RecvDate,
			BB
		FROM	@Stage
	ELSE
		select * from #DataList
END

correct result after i remove the while from inside IF


DECLARE	@Stage TABLE (RecID INT IDENTITY(1, 1), ItemStorageID VARCHAR(12), Qty MONEY, RecvDate DATETIME,BB char(1))


	WHILE (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) < @WantedValue AND @@ROWCOUNT > 0		
		INSERT		@Stage (ItemStorageID, Qty, RecvDate, BB)
		SELECT TOP 1	t1.ItemStorageID,
				t1.Qty,
				t1.RecvDate,
				t1.BB
		FROM		#DataList AS t1
		LEFT JOIN	@Stage AS s ON s.ItemStorageID = t1.ItemStorageID
		WHERE		s.ItemStorageID IS NULL
		ORDER BY	t1.RecvDate,
				t1.Qty DESC	

IF @pickType='FI'
	BEGIn	
	IF (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) >= @WantedValue
		SELECT	recID,
			ItemStorageID,
			Qty,
			RecvDate,
			BB
		FROM	@Stage
end

~~~Focus on problem, not solution~~~

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-18 : 00:42:08

So what isn't working? Are you getting an error? Unexpected result set? ...

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 00:56:23

I thing it is the @@ROWCOUNT that goes haywire.
See this original topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82143

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 00:56:47

Use the cursor approach I suggested to you!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 01:05:36

What's the point for me to suggest dozens of solutions when you do not use them because you want to rewrite them.
And when they don't work after rewrite, you come back here and ask why...

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-04-18 : 02:06:49

the thing is i dont easily rewrite.. coz usually i ask the part that im not able to do.. when it it solved still i had to add some part here and there.. sometimes it works, sometimes it cant... like the latest topic.. the solutions works great.. but i need to add some IF to suit 3 scenario..
1) the solution (orderby recvdate)-FIFO,
2) order by recvdate DESC (FIFO)
3) order by expdate (FEFO)

so i tot it will work if i put the while in this IF statement... coz is all the same so i put :-
IF @PickType='FIFO'
--while loop here

~~~Focus on problem, not solution~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 02:33:48

If you tell us the FULL picture, maybe you won't have to rewrite afterwards?
Have you thought of that?

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-04-18 : 02:49:40

ok back to the topic

~~~Focus on problem, not solution~~~

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-18 : 11:48:47

You haven't answered my questions.

Tara Kizer
http://weblogs.sqlteam.com/tarad/