local temporary tables

[Pattycake245]

I am working on a stored procedure where a local temporary table is being created when I ran across this in books online:

"A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table."

I create a couple of strings dynamically and then execute them one at a time. The first creates the temporary table, the second string populates the table and the third pulls data from that table. For whatever reason, after that table is created the second string will not execute because it says the temporary table is not a valid object. I've checked the spelling and everything is correct. So, do temporary tables exist until a strored procedure is finished or do they exist only inside the string which is creating it?

Any help or past experience to share regarding this would be appreciated?

Tim

 

[Pattycake245]

Never mind, I figured it out.

Tim

 

[MarvinJean]

How did you fix it - I have a similar problem

Thanks

Marvin

 

[Pattycake245]

I ended up doing all 3 sql queries inside one large query. This of course put the string over 4000 characters so I had to declare the string as varchar 8000 and execute that, but it worked. This is the only way I can see around this problem.