Sybase stored procedure bug!?! Due to renamed table

We were investigating an issue with a web page – that displays data from a Sybase database. The problem was simple – the data was not as expected. The values were missing.

When we queried the actual table (say TableA) from which the stored procedure selects, the information was there. But on running the stored procedure, there would be no results.

We first suspected the parameters that were being passed in – but they were fine.

We then ran the stored procedure with IO statistics On and traced the culprit. The stored procedure was selecting records from a totally different table (TableA_old). The logical explanation is this:

Somebody renamed TableA to TableA_old. Thereby breaking all stored procedures, views depending on it. Later this somebody made another table by the same name TableA. But that does not fix the things that were broken earlier by the renaming.

The stored procedure (which is precompiled) was still pointing to the old table since it was referencing the table using id’s used internally by the database.

We saw if there was an option to recompile as I remembered that there was a sp_recompile, but that did not help. I played around with sp_recompile and it did mark the table to cause all associated sp’s to be recompiled the next time it was run. But when I ran the stored procedure again, it recompiled (there were 2 IO Stats entry), but both were pointing to the old (renamed) table only.

We then dropped and re-created the stored procedure and that fixed it all.

Tthe Sybase version I was working on was quite an old one:
“Adaptive Server Enterprise/12.5.3/EBF”

Here is a quick way to find out what version you are in. SELECT @@version
To just give some code examples to reproduce this error, try this on some old version of Sybase:

CREATE TABLE tbl1
(
name VARCHAR(10)
)

CREATE PROCEDURE sp_tbl1
AS
SELECT name FROM tbl1

SET STATISTICS IO ON
EXEC sp_tbl1

/*
**RESULT WOULD BE:
Table: tbl1 scan count 1, logical reads: (regular=1 apf=0 total=1),
physical reads: (regular=1 apf=0 total=1), apf IOs used=0
**/

SET STATISTICS IO OFF
sp_rename 'tbl1','tbl1_old'

CREATE TABLE tbl1
(
name VARCHAR(10)
)

SET STATISTICS IO ON
EXEC sp_tbl1
/*
**RESULT WOULD BE:
Table: tbl1_old scan count 1, logical reads: (regular=1 apf=0 total=1),
physical reads: (regular=0 apf=0 total=0), apf IOs used=0
**/

DROP PROCEDURE sp_tbl1
DROP TABLE tbl1
DROP TABLE tbl1_old