467,146 Members | 1,339 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,146 developers. It's quick & easy.

Stored Procedure Temp Table Madness

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
  • viewed: 1537
Share:
1 Reply
"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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by C Kirby | last post: by
18 posts views Thread by Jarrod Morrison | last post: by
8 posts views Thread by Thomasb | last post: by
1 post views Thread by peaceburn@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.