SQL Server Forums
Profile | Register | 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

16 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
35952 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

16 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
35952 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

16 Posts

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

madhivanan
Premature Yak Congratulator

India
22713 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.05 seconds. Powered By: Snitz Forums 2000