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

SQL Server 2000 Forums

SQL Server Development (2000)

loop

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2006-02-22 : 10:43:21

have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks

SamC
White Water Yakist

3467 Posts

Posted - 2006-02-22 : 11:20:05

Loop through and do what?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-22 : 11:36:50

If you *need* to loop, then go ahead and use a cursor.

But I suspect that you don't really need (or want) to loop. 95 times out of 100 you can do whatever you need to do on all rows at once in a set-based manner.

What are you trying to do?

sardinka
Posting Yak Master

142 Posts

Posted - 2006-02-22 : 13:58:32

I need to do couple if statements based on a rowID and update some statuses in a table.
Below is my example:
ROWID Name NameID
1 a a1
2 b b2... etc

If NameID=a1 where RowID=2
begin...
If NameID=a1 where RowID=2
Begin... end
else...

end
else

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-22 : 15:21:05

Most likely there is a more efficient way of doing what you want. If you provide us with details and a small example it seems like a simple UPDATE statement will do the trick for you.

sardinka
Posting Yak Master

142 Posts

Posted - 2006-02-23 : 10:05:14

select
RowID,
NameLast,
PlanID,
Sex,
EffectiveDate,
Status
into #temp
from table1

if0<=(select count(*) from #temp a join table3 b on a.PlanID=b.PlanID))
Begin
if0<=(select count(*) from #temp a join table2 b on a.PlanID=b.PlanID and a.Effectivedate=b.EffectiveDate))
Begin ....End
Else
update #temp
Set status='Step2'
Where RowID=RowID
END

Else
update #temp
Set status='Step1'
Where RowID=RowID

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-23 : 10:13:58

Well, like I said, if you provide us with details and a small example, we'd be happy to help you.

Showing us segements of code that may or may not work doesn't really tell us what you need, does it?

sardinka
Posting Yak Master

142 Posts

Posted - 2006-02-23 : 10:30:39

I was trying to explain the logic I am going for. Tell me more what I need to give you in order to understand it.
thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-23 : 10:52:14

Ok, i'll help you if you help me.

I am having a similiar problem. Here is my code:

IF ((SELECT COUNT(*) FROM Table1) > 0)
BEGIN ... END
ELSE
BEGIN
IF ((SELECT ID FROM Table2) < 2)
SELECT ID FROM Table3
END

It doesn't work. Can you help me out?

sardinka
Posting Yak Master

142 Posts

Posted - 2006-02-23 : 13:22:29

what do I need you to help you with?
Your query is a basic counts from tabels what I need to do is apply some joins and where clause.
Can you tell me exactly what I need to give you in order to help me.
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-23 : 13:43:13

sardinka,

He is trying to show you that you haven't given enough information for us to help you. As you can see in his last post, he hasn't given enough information for us to help him as we have no idea what he wants.

You need to explain in words what you need to do and possibly show us a data example.

Tara Kizer
aka tduggan

sardinka
Posting Yak Master

142 Posts

Posted - 2006-02-23 : 14:27:14

Table Data:
RowID PlanID NameLast gender effDate Status
1 123 me f 01/01/06 NULL
2 245 you m 02/01/05 NULL
3 589 you1 m 03/02/06 NULL

Result:
RowID PlanID NameLast gender effDate Status
1 123 me f 01/01/06 Step1
2 245 you m 02/01/05 Step2
3 589 you1 m 03/02/06 Step3

Logic:
For row1:fail on step1 because effdate doesn't match with effectivedate in table 2 (join on planID)
For row1:fail on step2 because gender doesn't match with genderin table 5 (join on planID)

etc...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-23 : 14:32:42

You've shown one table of data, yet you mention at least 3 in your description. You mention that "effdate" doesn't match "effectivedate", yet there is no column anywhere you've given us named effectivedate. You also mention generIn, which again I don't see anywhere.

From what you've showed us, the answer is:

UPDATE Table SET Status = "Step" + Convert(varchar(1), RowID)

I doubt that's the answer.

The funny thing is, I bet that if you are able to give us 3 tables with good sample data (small, but covering the possibilities), clearly state what you need, and give a clear result based on that sample data, you'd probably see the answer jump right out at you.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-23 : 15:00:36

To see an actual sample of the information jsmith8858 is talking about, check this out:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

The set-based solution is the way to go and I encourage you to follow the instructions in this link and continue this discussion to get a set-based solution.

But to answer your original question about how to loop using a rowid (identity column), here is an example of one possible way:


declare @rowid int
select @rowid = min(rowid) from myTable
while @rowid is not null
begin
	print @rowid	

	select @rowid = min(rowid) 
	from myTable
	where rowid > @rowid
end

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources