Stored Procedure Temp Table Madness

BigD

This all centers around an Access Data Project

I have a stored procedure that aggregates events stored in a table
based on intervals I specify. I have a form that supplies parameters
for the stored procedure. My procedure creates a temp table to
populate with the results. The problem I have is that I cannot create
a form or report that uses the stored procedure as a source because
the temp table is dropped when the stored procedure is finished.

Now I understand from reading in these newsgroups and around the
internet that this is not an uncommon problem and I can overcome the
problem by building a real table and populating the data and
subsequently tearing it down but that just doesn't seem like it would
be the ideal solution.

Here is my procedure. Let me know if you have any ideas.

ALTER Procedure Aggregate_Events
@StartTime DateTime,
@EndTime DateTime,
@Aggregation Int
AS

SET NOCOUNT ON

Declare @Interval DateTime

Select @Interval = @StartTime
Create Table #Intervals (Interval DateTime Not Null, Logoffs Int)

While DateDiff(Minute,@Interval, @EndTime) > 0
Begin
Insert Into #Intervals
Select @Interval, Count(Event_DateTime)
From Events
Where DateDiff(Minute,@Interval,Event_DateTime)
Between 0 And @Aggregation - 1
Set @Interval = DateAdd(Minute,@Aggregation,@Interval)
End

Nov 13 '05 #1

John Winterbottom

"BigD" <db**********@yahoo.com> wrote in message
news:68**************************@posting.google.c om...

This all centers around an Access Data Project

I have a stored procedure that aggregates events stored in a table
based on intervals I specify. I have a form that supplies parameters
for the stored procedure. My procedure creates a temp table to
populate with the results. The problem I have is that I cannot create
a form or report that uses the stored procedure as a source because
the temp table is dropped when the stored procedure is finished.

As long as you're using the procedure itself as the recordsource and not the
table iteslf this should work.

Nov 13 '05 #2