Why isn’t there a Procedure Editor for Sybase ASE like there is for the other database vendors?

I can right mouse click on a procedure in Oracle, DB2 and SQL Server and choose the “Edit in Procedure Editor”, but in Sybase ASE this option doesn’t exist. Why is “Edit in Procedure Editor” missing in Sybase ASE?

Response

Niels Gron over 11 years ago
Aqua Data Studio has a Procedure Editor. The editor works with Oracle, DB2 and SQL Server, but it doesn’t work with Sybase ASE. The reason is because Sybase ASE lacks a specific feature that all of the other databases have. The Procedure Editor requires the ability to _alter_ the procedure building in an atomic transaction. For this, the database requires an ALTER PROCEDURE , CREATE OR REPLACE , or the support of DDL in transactions. Sybase ASE doesn’t support any of these, while the other databases support at least one. To edit a procedure in Sybase ASE, you need to Script a DROP and CREATE DDL of the procedure in a Query Window and manually DROP and CREATE the procedure.

If the DROP succeeds and the CREATE fails, then you will need to fix the error to CREATE the procedure. You can also configure the Script to generate a DROP and CREATE at the same time in File->Options->General->Scripting.

Peter Wendel over 11 years ago

Please have a look at this:

begin transaction
go
sp_drop_object ‘xxx’, ‘procedure’
go
create procedure xxx as select 1
go
drop procedure xxx
go
create procedure xxx as select 2
go
commit
go

Sybase ASE supports at least one of your required features… isn’t it?

Niels Gron over 11 years ago
This script example you provided does not work for me. It gives me the error :

The ‘DROP PROCEDURE’ command is not allowed within a multi-statement transaction in the ‘demo’ database.