By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Stored Procedure Temp Table Madness

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
"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.