Stored procedure temporary table problem?

I am using stored procedure to extract data from two different databases to a ASP.NET application. My stored procedure work as follows - Check if global temporary table exist or not say ##temp_table - if exist then drop it and if not create new temporary table say ##temp_table - Extract data from two different databases and fill it to Temporary table - Select data from temporary table - Drop temporary table

Now problem is that when number of users accessing the same page with same stored procedure as above then to some users it get error that temporary table already exist. Now please some one help me to solve such problem or suggest me some alternate because I don't want to write query in side ASP code. Some 开发者_如何学Cone suggest me to use views. Waiting for your suggestions.

"##" tables are accessible by all connections to the SQL instance, while "#" tables are accessible only by the connection that created them. The functionality you are describing sounds like you should be using "#" tables, not "##" tables.

You must not create table, you must declare variable for temporary table storage...with the column you expect to fill out...declare table variable like this:

declare @tmpTable table(myID int,myName varchar(50));

fill it like
insert into @tmpTable
Select * from table1

use it like
select * from @tmpTable