MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > MS SQL Development
Reload this Page

Select in a While Loop?

Discuss Select in a While Loop? in the MS SQL Development forum on Dev Shed.
Select in a While Loop? MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
  #1  
Old July 26th, 2004, 09:38 AM
jlencion jlencion is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: Minnesota
Posts: 4 jlencion User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Select in a While Loop?
Hello,

I am working on developing a calendar app and am having trouble with a stored procedure. I need to perform a select in a while loop (I think) and it only seems to return whatever it gets on the first cycle through the while loop. Is there a way to access the rest of the data or is it just not looping? Or, is there a better way to do this?

Code:
CREATE PROCEDURE get_events_today_iter
@todayStart varchar(64),
@todayEnd varchar(64)

AS

DECLARE @counter int, @loopStartDate varchar(64), @loopEndDate varchar(64)
SET @counter=0


WHILE (@counter <= DATEDIFF(DAY, @todayStart, @todayEnd))
	BEGIN
		SET @loopStartDate = DATEADD(DAY, @counter, @todayStart)
		SET @loopEndDate = DATEADD(SECOND, 1, DATEADD(DAY, @counter-1, @todayStart))
		
		SELECT DISTINCT e.id, d.stime, d.etime, e.title, c.name, @counter as counter, @loopEndDate as day, DatePart(hh, d.stime), DatePart(mi, d.stime)
		FROM calendar_events e, calendar_dates d, calendar_categories c 
		WHERE
			d.stime <= @loopStartDate
			AND d.etime >= @loopEndDate
			AND e.id = d.eid
			AND e.cid = c.id
			AND e.approved = 1
			AND e.status <> 0
			AND d.status <> 0
			AND e.calendar_id < 100
			AND e.calendar_id <> 3 
		ORDER BY DatePart(hh, d.stime), DatePart(mi, d.stime) ASC

		SET @counter=@counter + 1
	END
GO


And an example of the code I use to execute the procedure is:

Code:
EXEC get_events_today_loop '5/1/2004 11:59:59 PM', '5/4/2004 00:00:00 AM'


Any help would be greatly appreciated.

Thanks!
Reply With Quote
  #2  
Old July 26th, 2004, 11:08 PM
sonasonia sonasonia is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2004
Posts: 31 sonasonia User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 m 40 sec
Reputation Power: 5
Hi,

Please look into the reply of the following thread, it might help you
Quote:
http://forums.devshed.com/t135759/s.html
Reply With Quote
  #3  
Old July 27th, 2004, 09:32 AM
jlencion jlencion is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: Minnesota
Posts: 4 jlencion User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
not quite...
Thanks sonasonia for pointing me there,

I actually already looked at that and searched this forum but nothing seemed to be exactly what I was looking for. The difference between that one and my procedure is I am doing fine with selecting more than one row, in fact, I can get many rows with the procedure that I posted, but the problem is that I can only get to the data in the select on the first round through the loop. To be honest I don't even know if it is looping more than once.

What I need is either a better way to do this, or some way to perform multiple select statements in a stored procedure and be able to get to that data.

If you have any idea, your help would be greatly appreciated.

Thanks once again!
Reply With Quote
  #4  
Old July 28th, 2004, 02:32 AM
brightlight brightlight is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2004
Posts: 371 brightlight User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 19 h 32 m 27 sec
Reputation Power: 5
Hi,

I think you can create a temp table and insert the values which u select. Look into the following example. It is not exactly as yours but u can get an idea of creating a temp table and inserting values into it.

Quote:
declare @Part_Id int
declare @Category_Id int
declare @Desc varchar(50)
create table PART (Part_Id int, Category_Id int, Description varchar(50))
set @Part_Id = 0
set @Category_Id = 0
while @Part_Id < 2
begin
set @Part_Id = @Part_Id + 1
while @Category_Id < 3
begin
set @Category_Id = @Category_Id + 1
If @Category_ID = 3 and @Part_ID = 1
GOTO BREAK_OUT
set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) +
' Category_Id ' + cast(@Category_Id as char(1))
insert into PART values(@Part_Id,
@Category_Id,
@Desc )
end
set @Category_Id = 0
end
BREAK_OUT:
select * from PART
drop table PART


When u solve ur problem please paste ur solution for it will be useful for the others.
__________________
Regards,
Brightlight
Reply With Quote
  #5  
Old July 28th, 2004, 10:45 AM
jlencion jlencion is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: Minnesota
Posts: 4 jlencion User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Got it!
Thanks for the push in the right direction. I got it to work by selecting the information into a tempdb. Here's my procedure if anyone is interested:

Code:
CREATE PROCEDURE get_events_today_loop
@todayStart varchar(32),
@todayEnd varchar(32)

AS

DECLARE @counter int, @loopStartDate varchar(64), @loopEndDate varchar(64)
SET @counter=0

CREATE TABLE tempdb.dbo.calendarDates (id int, stime datetime, etime datetime, counter int, title varchar(256), [description] text, [name] varchar(128), tempa varchar(16), tempb varchar(16))

WHILE (@counter <= DATEDIFF(DAY, @todayStart, @todayEnd))
	BEGIN
		SET @loopStartDate = DATEADD(DAY, @counter, @todayStart)
		SET @loopEndDate = DATEADD(SECOND, 1, DATEADD(DAY, @counter-1, @todayStart))
		
		INSERT INTO tempdb.dbo.calendarDates
		SELECT e.id, d.stime, d.etime, @counter, e.title, e.description, c.name, DatePart(hh, d.stime) AS tempa, DatePart(mi, d.stime) AS tempb
		FROM calendar_events e, calendar_dates d, calendar_categories c 
		WHERE
			d.stime <= @loopStartDate
			AND d.etime >= @loopEndDate
			AND e.id = d.eid
			AND e.cid = c.id
			AND e.approved = 1
			AND e.status <> 0
			AND d.status <> 0
			AND e.calendar_id < 100
			AND e.calendar_id <> 3 
		ORDER BY DatePart(hh, d.stime), DatePart(mi, d.stime) ASC

		SET @counter=@counter + 1
	END

SELECT * FROM tempdb.dbo.calendarDates
DROP TABLE tempdb.dbo.calendarDates

GO
Reply With Quote
  #6  
Old August 26th, 2004, 09:49 AM
Blase Blase is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2004
Posts: 11 Blase User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
You should be getting multiple sets of records, one per iteration.

Depending on how you are using the stored procedure, say ADO recordsets, you should be able to look at all the data by doing recordset.getnextrecordset or whatever.

-Blasé

PS: Temp tables and cursors are a bad idea. Try not to use them, if you can help it. Here's a very link to a site of much wisdom >>> http://www.sql-server-performance.com
Reply With Quote
  #7  
Old September 14th, 2004, 12:38 PM
jlencion jlencion is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: Minnesota
Posts: 4 jlencion User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Hey thanks. I am using Coldfusion and I didn't know of a way to access the other recordsets, so I resorted to using the tempdb even though I knew I didn't want to.. But, with a little more prodding, I discovered a tag that I didn't even know about that let me do just that: cfstoredproc, cfprocparam, and cfprocresult. So, I just took the stuff out of the stored procedure that made it write to a temp db and changed my cfquery to cfstoredproc and everyone was happy.

thanks!
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > MS SQL Development > Select in a While Loop?

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway.