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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 looping over a select statment
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

igor22
Starting Member

18 Posts

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

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 03/02/2006 :  14:09:48  Show Profile  Visit tkizer's Homepage
Yes. If you post the code, we can help.

Tara Kizer
aka tduggan
Go to Top of Page

igor22
Starting Member

18 Posts

Posted - 03/02/2006 :  14:20:38  Show Profile
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

USA
38200 Posts

Posted - 03/02/2006 :  14:37:30  Show Profile  Visit tkizer's Homepage
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

Edited by - tkizer on 03/02/2006 14:54:19
Go to Top of Page

igor22
Starting Member

18 Posts

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

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 03/03/2006 :  01:43:38  Show Profile  Send madhivanan a Yahoo! Message
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
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000