I have a procedure, based on some query & conditions which returns 1 or zero for given user_id. It was working perfectly fine for my user_id (which is returning 1), but when I executed this proc today, it failed (returning 0). I checked the data in the table. Every thing was intact. Finally I recreated the procedure, it started working fine. Do you guys know why this weird behavior?. ( I vaguely remember other couple of procedure started behaving like this few days ago. I put some debug statements and recreated, then it started working). Do I have to re-create the procs regularly? How many days once?
the procedure depends on how the client program logs into Sybase. When opening a connection, a client can pass a string tot he server, and this string later gets stored as "program_name". Your stored proc checks this string.
Some clients use a hardwired string. For example the "isql" utility always passes a given name, and Artisan uses some other constant. In your case, you may have a client written in some other language, e.g. Java, Powerbuilder or Visual Basic. These languages can see the string, by passing a parameter usually called APPLICATION_NAME, or something like this, depending on the language.
In a nut-shell: the random behaviour of your stored proc is probably caused by changes in the client code.