temporary table from stored proc not getting drop

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

Transact-SQL (2000)

temporary table from stored proc not getting drop

Author

Topic

Vishakha
Starting Member

40 Posts

Posted - 2006-06-01 : 06:28:41

Here is what I am doing

create procedure <procname)

create table #table1( ....)

logic and queries and insert into #table1

create table #table2( .....)

logic and insert into #table2

drop #table1

create table #table3

logic and insert into #table2

drop #table2

select * from #table3;
drop #table3

I am getting the error, #table1 not found, though just the previous instruction populated data from table1 into table2.

Also is there a way I can reduce the number of temporary tables, I thought of using table variables but since the data is huge I will need indexing
Temporary tables store the in between data we need to create the reports.

Since this stored procedure will recompile very often, is it advisable to create small stored procedures inside the main stored procedure. Will it help in performance.

mr_mist
Grunnio

1870 Posts

Posted - 2006-06-01 : 06:53:43

Your pseudocode above doesn't do any inserting into #table3, though it would not cause your error message. Can you post the actual procedure code?

Why are you droping the tables? They will be destroyed anyway when the procedure goes out of scope.

-------
Moo. :)

Vishakha
Starting Member

40 Posts

Posted - 2006-06-01 : 08:59:22

sorry I missed the insert into table3.

The main reason to drop is because my temp tables contain huge amount of data so i want to remove it as soon as I am done with it.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-06-01 : 10:43:53

may be ur pseudo code doesn't reflect the real scenario. eg. the table may be created if a condition is satisfied. and in some instances, the table may not be created but u may be trying to drop it

The following works good, so check ur logic(s)

Create table #t(a varchar(3))
Insert into #t values('1')
Insert into #t values('ccc')

Select * from #t
Drop table #t

Srinika

mr_mist
Grunnio

1870 Posts

Posted - 2006-06-01 : 10:57:59

Still probably best if you can post the real procedure :)

-------
Moo. :)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-01 : 11:07:39

I can't believe no one noticed that "drop #table1" is not valid SQL syntax.


create table #table1( x int)
go
print 'bad'
go
drop #table1
go
print 'good'
go
drop table #table1

Results:



bad
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '#table1'.
good

CODO ERGO SUM

mr_mist
Grunnio

1870 Posts

Posted - 2006-06-01 : 11:12:49

I assumed he was paraphrasing his code, as the rest wasn't complete.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources