While loop that doesnt work - 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

SQL Server 2008 Forums

Transact-SQL (2008)

While loop that doesnt work

Author

Topic

gascan123
Starting Member

3 Posts

Posted - 2011-11-18 : 04:23:10

Hey,

I have a question for all the T-SQL Pros out there.
I would like to create a while loop with the following statement:

PRINT 'Start'

WHILE

(
SELECT COUNT (*)
FROM DWH_core.dbo.SCD_Zeiten vz1
INNER JOIN DWH_core.dbo.SCD_Zeiten AS vz2 ON vz1.Versicherter = vz2.Versicherter
AND vz1.id != vz2.id AND (vz1.Art = vz2.Art OR vz1.Art is NULL AND vz2.Art is NULL ) AND
(
(((vz1.Ende >= vz2.Beginn OR vz1.Ende IS NULL )) AND (vz1.Beginn IS NULL OR vz1.Beginn <= vz2.Beginn))
OR
(1 = Datediff(DAY, vz1.[ENDE], vz2.BEGINN) AND (vz1.Ende <= vz2.Ende OR vz2.Ende IS NULL) AND (vz1.Beginn IS NULL OR vz1.Beginn < vz2.Beginn))

)
) <> 0

BEGIN

PRINT 'Loop'

END

PRINT 'Done'

The SELECT query in the while LOOP executet seperatly needs about 1 seconds to complete.
However if I start the above SQL Script, it doesn't end. It prints Start and then nothing happens.
Neiter Loop or Done are getting printed, it seems like the SELECT query doesnt work.
Can anybody help me ?

Thanks in advance, Birger.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-18 : 04:46:22

The output of your print is to see in the output register "Messages" and not in "Result".
You can do SELECT 'Loop' instead of print 'Loop' to see it in "Result".
Your loop will never end because nothing is changing hence the count(*) will always be the same.

No, you're never too old to Yak'n'Roll if you're too young to die.

gascan123
Starting Member

3 Posts

Posted - 2011-11-18 : 05:52:45

That is correct, but I forgot to mention that Count(*) returns 0 so the loop should never be executed.

UPDATE to Main Post: "The script finally endend after 12 Minutes. Messages:

Start
Done

So the script seems correct but why does it need so much time ? As I said the Select Statement itself needs about 1 second to finish.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-18 : 06:11:48

I tried cleaning up the JOIN condition a little. Also if you only want to know if there are any results or not you could use a WHILE EXISTS () clause



WHILE EXISTS (
	SELECT 1
	FROM DWH_core.dbo.SCD_Zeiten AS vz1
	INNER JOIN DWH_core.dbo.SCD_Zeiten AS vz2 ON
		vz1.Versicherter = vz2.Versicherter 
		AND vz1.id != vz2.id
	
		AND (vz1.Art = vz2.Art OR ( vz1.Art is NULL AND vz2.Art is NULL ))
			
		AND (
				(	
					( vz1.Ende >= vz2.Beginn OR vz1.Ende IS NULL )	
					AND ( vz1.Beginn IS NULL OR vz1.Beginn <= vz2.Beginn )
				)
				OR (
					DATEDIFF(DAY, vz1.[ENDE], vz2.BEGINN) = 1
					AND ( vz1.Ende <= vz2.Ende OR vz2.Ende IS NULL )
					AND ( vz1.Beginn IS NULL OR vz1.Beginn < vz2.Beginn )
				)

			)
		)

This join is pretty horrible. Are you sure it's what you want to do? Looks like you are trying to join each row to all earlier versions of some entity.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

gascan123
Starting Member

3 Posts

Posted - 2011-11-21 : 03:43:42

Ok I tried the WHILE Exists() solution:

Same problem, the SELECT statement needs 1 second to finish, the WHILE Exists() about 9 minutes. Does anybody know why ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 03:59:39

the join it self looks like its having a hidden RBAR triangular join. Perhaps you could explain what you're trying to check using this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources