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.
| Author |
Topic |
|
chedderslam
Posting Yak Master
223 Posts |
 Posted - 2002-09-18 : 21:51:43
|
| I am crawling down a recursively joined table(parentFK points to tablePK of same table).How do I break out of the loop? I've tried @@rowcount, but that spikes to 100% cpu infinite loop. I do CF, so I could usually use qryName.recordcount, but I'm not sure of the equvilent in t-sql. I've also tried setting @@rowcount to a local var with no sucess.Here's the code. A little smattering of CF code in their, but I shouldn't be too hard to decipher. Just ignore anything in octothorpes:SET NOCOUNT ON DECLARE @tblVar#listFirst(GetFileFromPath(GetCurrentTemplatePath()), ".")# TABLE ( tablePK int identity(1,1), tblProductTaxologyPK varchar(10) ) DECLARE @continue int INSERT INTO @tblVar#listFirst(GetFileFromPath(GetCurrentTemplatePath()), ".")# SELECT tblProductTaxologyPK FROM #request.sqlObjectPrefix#tblProductTaxology WHERE parentFK = <cfqueryparam value="#attributes.taxologyKeyList#" cfsqltype="CF_SQL_INTEGER"> SET @continue = @@ROWCOUNT WHILE @continue > 0 BEGIN INSERT INTO @tblVar#listFirst(GetFileFromPath(GetCurrentTemplatePath()), ".")# SELECT tblProductTaxologyPK FROM #request.sqlObjectPrefix#tblProductTaxology WHERE parentFK IN ( SELECT DISTINCT tblProductTaxologyPK FROM @tblVar#listFirst(GetFileFromPath(GetCurrentTemplatePath()), ".")# ) SET @continue = @@ROWCOUNT END SET NOCOUNT OFF SELECT * FROM @tblVar#listFirst(GetFileFromPath(GetCurrentTemplatePath()), ".")# |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-09-19 : 04:28:13
|
| As I understand your last piece of code, it's impossible for @CONTINUE to reach the value zero. In your WHILE loop you don't subtract any value from your first @@ROWCOUNT value. In addition you're setting it again at the end portion of your while loop?Check this first. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-19 : 05:11:07
|
| try putting a 'and not exists' clause in the insert to check if the entry is already in the table.You are adding duplicate entries which is why @@rowcount will never get to 0.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
recursive select loop break