We help IT Professionals succeed at work.

Sybase stored procedure: Problem when loading stored procedure containing a temp table.

Medium Priority
Last Modified: 2012-05-05
We are attempting to load a stored procedure that references a temp table.  The problem is that Sybase is pre-compiling the stored procedure, and it is erroring because the temp table hasn't been created yet.  How can we load this stored procedure, while still being able to use temp tables in the stored procedure?
We are loading the stored procedure from the command line using isql -i[filename]...
Watch Question

Top Expert 2011

what is the actual error message?

i'm more  familiar with SQL Server ... is this a global temporary table?

is it a real problem ?  is the message a warning rather than an error message?


It's not a global temp table.  It is an error message, as follows:

Msg 208, Level 16, State 1: Line 4: #temp1 not found.  Specify owner.objectname or user sp_help to check whether the object exists (sp_help may produce lots of output).

It's a valid error message, because the table really does not exist, so sp_help returns nothing.  The temp table is created in the stored procedure itself.

Any help would be appreciated.  Thanks.
I suspect you have a situation where sp "A" creates a the table #temp1 and then calls sp "B" which refers to #temp1.  This is not that unusual a construct; it does create two problems though.

The first is the one you run into which is "how do I get it compiled?"  That is actually pretty simple.  The trick is to have a script that creates the temporary table with no rows in it just prior to creating the stored procedure.  For example...

SELECT Fld1, Fld2, .....
INTO #temp1
FROM SomeOtherTable
WHERE 1 = 2

This technique works best when your temporary table is based primarily on the field names and data types of an existing database table or tables.  If that is not the case, you can always to an explicit create table...

CREATE TABLE #temp1 (Fld1 varchar(32), Fld2 int,....)

You then immediately issue the create procedure statement.


This works because a temp table lives for the life a connection or the life the the stored procedure it was created in.  Since you are making the #temp1 table outside of a proc, it lives long enough to help you compile the "B" procedure.

Usually I clean up with a drop table statement so that I don't cause a problem with the next procedure that might try and create #temp1.

The other problem you have is the need to control the order in which the stored procedures are created.  In order for the database to keep track of dependencies, you need to create the called procedure "B" before you create the calling procedure "A", otherwise you get a warning message when compiling "A" that says that it could not find the object "B" and could not make the sysdepends entry.  Just a warning and the procedure compiles anyway.



Excellent!!!!  It worked, thanks for your help!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.