Retrive data from Temp table after SP closes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Retrive data from Temp table after SP closes

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-08-10 : 15:58:18

Hello,

I have a stored procedure that stores data in a table test1 during processing, and from this table a second procedure generates totals. Works ok, but in a multi-user environment there is the risk that two users can access the table simultaneously.

I know local temp tables are user specific, so if I


create #test1(col1 etc....

in a stored procedure only the current user will see this table data, but the problem is that when the stored procedure which generates the totals is subsequently called the temp table has already been deleted.

Is there anyway that I can create tables which can only be accessed by the current user?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-10 : 16:02:57

But the called stored procedure can still see the temp table in the calling stored procedure. Here's an example:

create proc sp2
as
set nocount on

select * from #temp1
return
go

create proc sp1
as
set nocount on

create table #temp1
(
column1 int not null
)

insert into #temp1 values(1)
insert into #temp1 values(9)
insert into #temp1 values(2)
insert into #temp1 values(3)

exec sp2

return
go

exec sp1

drop proc sp1
drop proc sp2

sp1 creates #temp1. sp2 is called from within sp1. sp2 can see #temp1. #temp1 does not get deleted until AFTER sp1 is done.

Tara

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-08-10 : 16:20:51

Hello,

In my case the results from sp1 and sp2 are used as the recordsource for report1 and report2. From the example shown I don't think I will be able to retrieve the two distinct recordsets that the reports need.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-10 : 16:22:08

I would need to see an example.

Tara

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-10 : 16:32:16

I'm pretty sure that Tara is correct. If your procedure #1 creates the temp table(s), then calls procedure #2 which populates them, I think you'll be "in business" for what you've described.

-PatP

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-08-10 : 16:54:39

Hello,

Let me try an example:

sp1


CREATE PROCEDURE TArrivalsByAGE

@PrevYear1 varchar(4),@CurrYear1 varchar(4), @Gdate int

AS
set @Gdate = (select year(getdate()))

delete from TempTArrivalsByAGE

INSERT TempTArrivalsByAGE SELECT  agegroup,January,February,March 
FROM dbo.AgeCrosstabFunc(@PrevYear1,@CurrYear1,@Gdate)

SELECT agegroup, 1 AS Quarter, January AS A, February AS B, March AS C
FROM TempTArrivalsByAGE
GO

sp2


CREATE PROCEDURE TArrivalsByAGE_Crosstab


AS

SELECT agegroup, January,February,March FROM TempTArrivalsByAGE
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-10 : 17:04:47

TempTArrivalsByAGE is not a real temporary table though. It exists for all users.

Tara

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-08-10 : 17:26:31

Hello,

Remember that I wish to convert to using temp tables, the issue being that if I do this, the temp table created in sp1 is deleted when that sp is closed and thus is unavailable for use by sp2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-10 : 17:28:21

Why can't you just call sp1 instead to get the data? Why do you need to call sp2?

Tara

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-08-10 : 17:37:26

Hello,

Performance issue, I would need to call sp1 twice which doubles my report time significantly.

Other alternatives I have been thinking about are:

1. Create user specific tables for each user who connects to the application database. Maybe prefix the table names with the workstation/user id to ensure unique names and delete the tables when finished.

2. Lock the table? So that while one user is accessing it another user has to wait until the results are returned.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-10 : 17:42:09

Option 1 would require dynamic sql, which is going to be a performance hit.
Option 2 is going to be a performance hit for anyone trying to access the table while it is locked.

Maybe we need to understand more of your design in order to help. So you've got two reports, one calls sp1, the other calls sp2? Why do you need a table? Why can't you just do this in sp1:

SELECT agegroup,1 AS Quarter,January AS A,February AS B,March AS C
FROM dbo.AgeCrosstabFunc(@PrevYear1,@CurrYear1,@Gdate)

Tara

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-08-10 : 17:58:31

Hello,

Sure, let me walk you through the design issues. The actual query used to be:


SELECT agegroup,1 AS Quarter,January AS A,February AS B,March AS C
FROM dbo.AgeCrosstabFunc(@PrevYear1,@CurrYear1,@Gdate)
union
SELECT agegroup,2AS Quarter,April AS A,May AS B,June AS C
FROM dbo.AgeCrosstabFunc(@PrevYear1,@CurrYear1,@Gdate)

and so on to December. Issue being that AgeCrosstabFunc was being called four times, plus again by report2, so the report would take really long to display.

By using the table, AgeCrosstabFunc is only called once, so the performance has improved significantly.

In relation to creating the tables, I could probably write a script to create all the tables the user may need when the application is first setup, so it wouldn't be a case where they are being created everytime the application is run.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources