Temp Table Scope

[nerdalert1]

Hello all. One more question for the day. I have some temp tables I need to create but they would be used in more than one proc. If I run a Proc that creates the temp tables within another proc I wont have scope of those temp tables that are created from the proc called correct? Is there a way to somehow keep those as Global Temp tables so I can call one Proc off the Start and then use it in another? If they do stay in scope somehow then I would just call it as the first step from my main proc. Thanks all.

 

[SQLSister]

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.

Questions about posting. See faq183-874

 

[nerdalert1]

hmmmm. Thats confusing . So its saying No then right? It says "The table can be referenced by any nested stored procedures executed by the stored procedure that created the table".

 

[donutman]

Yes that's correct and it is confusing. You should see the one about Global temp tables! But Global is what you want. Just use ## in front of the name. You should delete the table when your finished although it can go out of scope and destruct too.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

If you nest stored procedures, the temp table can be created inteh main one and then refernced in the nested sps as long as you never run them by themselves.

What you can't do is create a temp table in one of the subordinate sps and refernce it in the main one or another one called later in the main sp.

Questions about posting. See faq183-874

 

[nerdalert1]

Cool. So in my main proc I can call a proc that creates 4 temp tables. I am going to drop 3 of them right within that proc. The 4th one I need to call from within the main proc and use it in some select queries. Will that work? Thanks all.

 

[nerdalert1]

How do I call another proc in a procedure and pass 2 parameters to it?

 

[nerdalert1]

I complete this line in my main proc off the bat.

EXECUTE dbo.usp_INS_MAINTEMPSETUP @STARTD, @ENDD

In that Proc I create a temp table called #tmpDates

Then in the main proc I am referencing this table in FORM clauses. I am getting the error.

Invalid object name '#tmpDates'.

 

[SQLSister]

No create the temp tables in the main procedure.
You call an sp in a procedure the same way you call it from QA.

Exec USP_yourstoreprocedure 100,'test'
if you need the results in the main sp, insert the results into a temp table using an insert statment. I don't believe select into works with sps as the data source.



Questions about posting. See faq183-874

 

[SQLSister]

You must set the temp tables inteh main sp, not a subordinate one.

Questions about posting. See faq183-874

 

[nerdalert1]

That stinks. See what I am doing is creating a Table that goes off of other temp tables. The table I need however I need to reference in more than one main proc. 2 actually. I did not want to build the temp table code in both places. Is there another way you can think of?

 

[SQLSister]

NOt knkowing what you intend to do, I would suggest you look at table-valued user-defined funtions. They have some limitations, so they may not work with what you want to do, but you can see if they will.

Questions about posting. See faq183-874

 

[nerdalert1]

Thanks