How to create private temp tables within a stored procedures?

Question

Hi,

Using SQL server 2005. Please look at this test SQL which creates 2 stored procedures. The second uses the first. They both uses a temporary table but it isnt really private so it seems. How can I create a true private table that no other stored procedures has access to? 
 

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TestSp1]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[TestSp1]
go

CREATE PROCEDURE [dbo].[TestSp1] AS
Begin 
	if object_id('tempdb..#tmpTable') IS NOT NULL
		drop table #tmpTable

	create table #tmpTable
	(
		id int
	)
	
	insert into #tmpTable (id) values (1);
	insert into #tmpTable (id) values (2);
	
	select id from #tmpTable
	
	if object_id('tempdb..#tmpTable') IS NOT NULL
		drop table #tmpTable
end
Go

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TestSp2]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[TestSp2]
go

CREATE PROCEDURE [dbo].[TestSp2] AS
Begin 
	if object_id('tempdb..#tmpTable') IS NOT NULL
		drop table #tmpTable

	create table #tmpTable
	(
		id int
	)
	
	insert into #tmpTable (id)
	exec [dbo].[TestSp1]
end
Go

exec [dbo].[TestSp2]

 
The above errors with this: "INSERT EXEC failed because the stored procedure altered the schema of the target table.". So it seems that the temporary table created is the same used in both procedures. What am I doing wrong? I need 100% private temporary tables without having to name them in some unique way.

--
 

Answer 1

Oh I forgot: It works if I delete the "drop table" at the beginning of the SP's. To me, those those lines are unnescessary - they should not matter. I cant see why those lines makes things foobar.

Answer 2

It is expected. In your Second SP, you are trying to insert  some values to #tmpTable by calling the first SP which delete  same #tmpTable.
Temporary tables are session specific not object specific. You can use different name for both of your temperory tables  like  #TestSp1 in first SP and #TestSp2 in second SP..

Answer 3

It is expected. In your Second SP, you are trying to insert  some values to #tmpTable by calling the first SP which delete  same #tmpTable.
Temporary tables are session specific not object specific. You can use different name for both of your temperory tables  like  #TestSp1 in first SP and #TestSp2 in second SP..

Hm ok I thought they were object specific.

An alternative could be using table varables like "declare @tmpTable table ( ... )". But I'm concerned about the limitations. For example Im reading on this page that I cannot use table variables with "insert into". However if I change my example code from above to table variables it works alright...?? Is this because Im testing on SQL server 2008 (the article link is referring SQL server 2005).

?

Answer 4

Hi Werner,

When you create a temporary table in a procedure ([dbo].[TestSp2]), it is visible through out the execution of that procedure. So when you call [dbo].[TestSp1] from within [dbo].[TestSp2], the temporary table is still visible in the called stored procedure. So, dropping the table in the first line actually drops the table created in the outer procedure [dbo].[TestSp2]. This is detected as a schema change and so SQL Server throws this as an error.

As you have mentioned, if you comment the DROP statement and leave the code, the next statement creates another temporary table, but the temporary table will be different from the one created in [dbo].[TestSp2] and the DROP statement will drop the procedure created inside the called procedure. Since the table created in the outer procedure is not modified, no error is reported.

Let us know if you still have any questions. 

Regards

Padmanabhan 

Answer 5

Let us know if you still have any questions. 

Regards

Padmanabhan 

Thanks, actually I have one more question. I turn my head to table varaibles now. From this link I read that you should use table variables unless theres a  reason for using temporary tables (very large data, index uses etc). However it also says that "Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement". What does that mean? Something like "insert into @tmpTable exec mySp" does in fact work...or do I misunderstand that sentence?

Answer 6

Yes, you can't use a table variable with INSERT EXEC or SELECT INTO.
So, this is not allowed:

INSERT @table EXEC sp_someProcedure 
SELECT * INTO @table FROM someTable

Answer 7

Yes, you should try to use table variables in cases where the dataset is very small (this is very tricky). Temp tables are effective as they can be indexed and there are loads of other advantages. This article might help http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

The article you have mentioned starts saying "INF: Frequently Asked Questions - SQL Server 2000 - Table Variables". Not sure if you missed this :)

Cheers

Padmanabhan

Answer 8

Yes, you should try to use table variables in cases where the dataset is very small (this is very tricky). Temp tables are effective as they can be indexed and there are loads of other advantages. This article might help http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

The article you have mentioned starts saying "INF: Frequently Asked Questions - SQL Server 2000 - Table Variables". Not sure if you missed this :)

Cheers

Padmanabhan

Ah - important stuff there :)