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

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]...
gtflyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
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?
0
gtflyAuthor Commented:
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.
0
grant300Commented:
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.

CREATE PROCEDURE MyProc
AS
BEGIN
......
END

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.

Regards,
Bill
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gtflyAuthor Commented:
Excellent!!!!  It worked, thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.