The PCC indeed had a problem with the GUI interface surrounding the stored procedures. If you click SAVE and there are any syntax errors in the SP, then you get an unusual "ERROR" line back out of the PCC and the SP is completely lost.
Instead of right-clicking on the Stored Procedures line and selecting New Procedure, create a new SQL window instead. Inside this window, enter the CREATE PROCEDURE statement text as you normally would. When complete, you can directly run your stored procedure statement just like any other statement. If it fails, you will still have your original text to modify it.
If you need to edit the text of an existing SP, then use the PCC to read the current SP text from the database, then copy it over to a SQL window. Add the line "DROP PROCEDURE <procname>;" at the head of the script file, make your changes, and then "Run All SQL Statements". Your script will drop the original procedure and then recreate it just fine.
BTW, I also recommend saving your SP scripts as simple SQL text files -- makes manipulating them later on a LOT easier.
Main Topics
Browse All Topics
by: mirtheilPosted on 2009-11-02 at 13:39:04ID: 25724036
What version of v9 are you using? Make sure you are using the latest patch (v9.7) I know that when I create a procedure, I just Execute the query into Grid or Text mode. That should execute to create the procedure.
Granted, I usually use ODBC Test rather than PCC (it loads faster).