PDA

View Full Version : Temp table in store procedure

j1c4b
03-18-02, 18:01
Hi everybody
I am big store procedure which create a temp table during execution and then does select into and insert in this temp table. I am trying to do these steps in another store procedure which is called from the first one. But when I am trying to save second store procedure I am getting the error - "#temp_table is not exists" even thow I run create temp_table statement before save. Any Ideas why I can't save it?
MattR
03-18-02, 20:10
Do this:

Use create table to create the temporary table.
Create the procedures that access the temporary table, but do not create the procedure that creates the table (the 'called' or secondary procedure in your case).
Drop the temporary table.
Create the procedure that creates the table and calls the procedures created in step 2 (the original stored procedure).


http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/19957;pt=19957#X
dorjan_bee
03-19-02, 08:05
Usually, temp tables are named with a leading #, like #temp_table. These tables are created in tempdb, and automatically dropped after the stored procedure has done it's job. Give the table a name without leading #, and make sure that you are working in the same database where the table is created, or set a reference to the database like: select * from MyBase..temp_table