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
 Old Forums
 CLOSED - General SQL Server
 looping over a select statment

Author  Topic 

igor22
Starting Member

18 Posts

Posted - 2006-03-02 : 14:07:29
Can you look over the values of a select statment using the while loop?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-02 : 14:09:48
Yes. If you post the code, we can help.

Tara Kizer
aka tduggan
Go to Top of Page

igor22
Starting Member

18 Posts

Posted - 2006-03-02 : 14:20:38
Ok but please forgive me I'm very new at this:

CREATE PROC GetUsersItems
(

AS

Declare @HoldIds int,

SET @HoldIds=Select userid from users

WHILE (@HoldIds)-- Values from the query

Select * From items where userid =@HoldIds

END

Go
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-02 : 14:37:30
This should get you going:


Declare @HoldIds int

Select @HoldIds = MIN(userid)
from users

WHILE (@HoldIds <= select max(userid) from users)
begin
Select * From items where userid =@HoldIds

Select TOP 1 @HoldIds = userid
FROM users
WHERE userid >= @HoldIds
ORDER BY userid
END


The key is that @HoldIds can only hold one value at a time, so you must loop through the table.

Tara Kizer
aka tduggan
Go to Top of Page

igor22
Starting Member

18 Posts

Posted - 2006-03-02 : 14:49:19
Thanks a lot!
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-03 : 01:43:38
Is using Join enough?

Select I.* From items I inner join Users U on I.Userid=U.userId

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -