Altering Stored Procedures in ASE Sybase 15.7

Question

I am new to ASE Sybase 15.7 but do have some background in other RDBMS systems. So i assumed there would be an equivalent of CREATE OR REPLACE for Stored procedures in ASE Sybase 15.7.

But I dont seem to see any way to do this. Most people i have asked suggest dropping and creating with the newer version of the stored procedure but that gives me a challenge of managing the permissions on the stored procedure which are different across environments depending on the users in each.

So My ask is below:

Suppose I have a stored procedure as so:

ENV1

CREATE Procedure test (
as
begin
SELECT getdate()
end
grant execute on test to group1
go
grant execute on test to group2
go

ENV2 has :

CREATE Procedure test (
as
begin
SELECT getdate()
end
grant execute on test to group1
go
grant execute on test to group2
go
grant execute on test to group3
go

I want to update this stored proc to give me 2 dates instead of 1 so new proc should be

ENV1:

CREATE Procedure test (
as
begin
SELECT getdate(), getdate()
end
grant execute on test to group1
go
grant execute on test to group2
go

ENV2:

CREATE Procedure test (
as
begin
SELECT getdate(), getdate()
end
grant execute on test to group1
go
grant execute on test to group2
go
grant execute on test to group3
go

Above is a very simplistic example ofcourse. Is there a way to deploy the changes to just modify the stored procedure body preserving the permissions?

CREATE or REPLACE and ALTER PROCEDURE dont seem to work and dropping and creating the stored procedure would mean additional logic for each environment to figure out the permissions to be granted.

Is there a way to do this kind of deployment in an optimum way considering we have 20 plus different user environments?

Thanks!

Answer 1

While ASE does support create or replace, this is only available with ASE 16.x (ie, you'd need to upgrade to ASE 16.x).

Assuming you're looking to build some sort of scripted solution, I'd recommend taking a look at the ddlgen utility to assist with extracting the current permissions for a stored proc.

One (very simple) example of using ddlgen to pull the DDL for a stored proc:

$ ddlgen -SmyASE -Umylogin -Pmypassword -TP -Nsybsystemprocs.dbo.sp_help -Osp_help.ddl.out
$ cat sp_help.ddl.out
-- Sybase Adaptive Server Enterprise DDL Generator Utility/1 ...snip...
...snip...
use sybsystemprocs
go
...snip...
create procedure sp_help
...snip...
Grant Execute on dbo.sp_help to public Granted by dbo
go
sp_procxmode 'sp_help', anymode
go

From here you could grep out the desired grant, revoke and/or sp_procxmode lines, to be (re)executed once you've dropped/created the replacement stored proc.

---

If you don't have access to ddlgen (I know it's included in the ASE installation software but don't recall if it's provided in the SDK/client software installation) you have a few alternatives:

• have the DBA run the ddlgen commands for you and provide you with the results (yeah, I'm sure the DBA will love that idea)
• get ddlgen installed on your 'client' machine (eg, install the ASE installation package; or copy over just the needed files from an ASE installation - easier said than done, and would be a PITA when it comes to upgrading the software)
• run sp_helprotect <proc_name> (and sp_procxmode <proc_name>) and parse the output for the desired grant, revoke and/or sp_procxmode commands

And one alternative on the 'run-and-parse sp_helprotect/sp_procxmode output' ... look at the source code for these procs and roll your own SQL code to extract the desired data in a format that's easier for you process to handle.