Loop Through A Table

[JohnEOasis]

I have a function that I need to pass 3 fields in a query to. The function accepts EventID StartDate and EndDate and returns each day of the week. I need to get ALL of the EventIDs and Start and End dates from the events table. I an not figure out how to loop through the table to pass each of these fields from a query. Any help would be appreciated.

 

[OlafBogus]

Hi JohnEOasis,

sorry if i missunderstand the question but:

To loop through a query output table use a cursor.
The example below will read each row of an eventsTable
reading the EventID,StartDate and EndDate into 3 variables

Declare your cursor for the table
and the variables to hold your values

declare @EventID int
declare @StartDate datetime
declare @EndDate datetime
declare _Cursor cursor for select EventID, StartDate, EndDate from eventsTable

Then open the cursor and read the first row of data in:

open _cursor
fetch next from _cursor into @EventID,@StartDate,@EndDate

Then loopthrough the table reading each row of data:

while @@fetch_status = 0
begin
//Do your function calls etc using the fetched variables.
fetch next from _cursor into @EventID,@StartDate,@EndDate
End

Then release the cursor

close _cursor
deallocate _cursor

Cheers
OB

 

[bborissov]

Could you be more specific?
LOOPing through table is not a good idea. Can't you did what you want to do with set based commands?
Cursors are notorious performance killers.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[OlafBogus]

Indeed if you can do what you want without iteration via set based commands then that would be preferable due to the performance problems associated with Cursors.

 

[JohnEOasis]

I apologies I am a newbie when it comes to SQL. I am not sure what a set based commands are. Do you all have any examples or sites you would suggest?
What I am trying to do is pass the EventID, StartDate and EndDate to a function called GetDays that accepts these values.

 

[bborissov]

And then what?
What this function returns and what you want to do with the returned value.
Set based commands are these that works with bunch of records like SELECT, UPDATE, INSERT. SQL Server is optimized to works with this. When you work with cursors you work with one records at time and that slow the performance of your queries and your application.
So if you give us some example data and what you want as a final result we may (just may, because sometimes it is not possible to use set based commands) give you a solution that uses these commands.
BTW combination with CURSOR and UDF is killer!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JohnEOasis]

The function returns a table with all the names of the days between the start and end dates. What I want to do then is join it with other tables based on the event id so I can display the data in a report.
SELECT GetDayNames.EventID, GetDayNames.WeekDay, GetDayNames.CurrentDate, dbo.events.name
FROM dbo.fn_GetDayNames('1892e987-d289-ec32-d51e-154a31c915ec', '9/52008 1:00:00 PM', '9/14/2008 1:00:00 PM') AS GetDayNames INNER JOIN dbo.events ON GetDayNames.EventID = dbo.events.eventstid
What I would like to see is:
EventID DayName Date Name
_________________________________________________________________________
1892e987-d289-ec32-d51e-154a31c915ec Sunday 9/14/2008 1:00:00 PM Test Event 8
1892e987-d289-ec32-d51e-154a31c915ec Saturday 9/13/2008 1:00:00 PM Test Event 8
1892e987-d289-ec32-d51e-154a31c915ec Friday 9/12/2008 1:00:00 PM Test Event 8
1892e987-d289-ec32-d51e-154a31c915ec Thursday 9/11/2008 1:00:00 PM Test Event 8
1892e987-d289-ec32-d51e-154a31c915ec Wednesday 9/10/2008 1:00:00 PM Test Event 8
1892e987-d289-ec32-d51e-154a31c915ec Tuesday 9/9/2008 1:00:00 PM Test Event 8
1892e987-d289-ec32-d51e-154a31c915ec Monday 9/8/2008 1:00:00 PM Test Event 8
1892e987-d289-ec32-d51e-154a31c915ec Sunday 9/7/2008 1:00:00 PM Test Event 8
1892e987-d289-ec32-d51e-154a31c915ec Saturday 9/6/2008 1:00:00 PM Test Event 8
1892e987-d289-ec32-d51e-154a31c915ec Friday 9/5/2008 1:00:00 PM Test Event 8

For each eventid in the events table.

 

[bborissov]

You didn't have to have such function, you must have one table with numbers in it from 0 to ?? No matter how many. Then you could use this:

---- Preparing test data
----- this is just a table with some numbers from 0 to 1000
DECLARE @Test TABLE (Number Int)
DECLARE @i int
SET @i = 0
WHILE @i <= 1000
      BEGIN
        INSERT INTO @Test VALUES (@i)
        SET @i = @i + 1
      END
------

--- That table simulates your Events table
DECLARE @Test2 Table (EventId uniqueidentifier, StartDate datetime, EndDate datetime)
INSERT INTO @Test2 VALUES (NEWID(), '20080101', '20080212')
INSERT INTO @Test2 VALUES (NEWID(), '20080601', '20081212')
------------------------


SELECT Test2.EventId,
       DateName(dw, DateAdd(dd, Test.Number, Test2.StartDate)) AS DName,
       Test2.StartDate,
       Test2.EndDate,
       DateAdd(dd, Test.Number, Test2.StartDate) AS DNumb
FROM @Test2 Test2
INNER JOIN @Test Test ON DateAdd(dd, Test.Number, Test2.StartDate) <= Test2.EndDate
ORDER BY Test2.EventId, 5

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JohnEOasis]

This is for MS CRM and adding tables is not supported so I am trying my best from adding tables.

 

[bborissov]

How you execute your code?
Is this a part of SP or something?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JohnEOasis]

It is a MS Report Server File (RDL).

 

[bborissov]

If you can't create table the use these whcih are already there

--- That table simulates your Events table
DECLARE @Test2 Table (EventId uniqueidentifier, StartDate datetime, EndDate datetime)
INSERT INTO @Test2 VALUES (NEWID(), '20080101', '20080212')
INSERT INTO @Test2 VALUES (NEWID(), '20080601', '20081212')
-------------------------------

SELECT Test2.EventId,
       DateName(dw, DateAdd(dd, Test.Number, Test2.StartDate)) AS DName,
       Test2.StartDate,
       Test2.EndDate,
       DateAdd(dd, Test.Number, Test2.StartDate) AS DNumb
FROM @Test2 Test2
INNER JOIN (SELECT (a.Number * 256) + b.Number AS Number
                   FROM  (SELECT number
		                        FROM master..spt_values
		                  WHERE type = 'P' AND number <= 255) a (Number),
	                     (SELECT number
                                FROM master..spt_values
		                  WHERE type = 'P' AND number <= 255) b (Number)) Test
ON DateAdd(dd, Test.Number, Test2.StartDate) <= Test2.EndDate
ORDER BY Test2.EventId, 5

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.