We are migrating to SA16 from SA9. Some of our stored procedures have temporary tables created as:
CREATE PROCEDURE proc1(IN var1 varchar(10)) BEGIN CREATE TABLE #TEMP (field1 varchar(10)); ...... END
Please note that there is no explicit DROP TABLE #TEMP in these stored procedures.
Now there are several other stored procedures with the same temporary table name in the same db:
CREATE PROCEDURE proc2(IN var2 varchar(20)) BEGIN CREATE TABLE #TEMP (field2 varchar(20)); .... END
After SA16 migration these stored procedures are throwing an error after they are called in a sequence from the application.
"Database error code: -1397 Select Error: SQLSTATE = S1000 [Sybase][ODBC Driver][SQL Anywhere]The definition of temporary table '#TEMP' has changed since last used."
I understand that we need to explicitly DROP the tempory table or use DECLARE to create temporary tables as the scope of these tables is within the stored procedure only. My question is this actually worked in SA9 without any error.
There are hundreds of such stored procedures and this is preventing us to migrate to a new version. Adding explicit DROP temporary table in these procedures is a massive exercise.
asked 18 Sep '13, 09:24