Best Practices for Migrating to SAP Sybase ASE – creating your own stored procedure template

Last blog we introduced a template for writing triggers as a best practice for the ASE Server.  In this blog we will continue the theme by introducing a best practices template for writing your stored procedures.  This is just one example of the variety of coding templates out there.

For those of you who are new to the ASE Server, a primer on stored procedures follows immediately below.  For those of you that know it all….go right to the template code  below.

A stored procedure is a collection of SQL statements and optional control-of-flow statements stored under a name. The creator of a stored procedure can also define parameters to be supplied when the stored procedure is executed. There are two steps to using stored procedures:

1. Create the procedure.  Syntax is “create procedure…..”  (This is our template)
2. Run the procedure.  Syntax is “exec <database>.<owner>.procedure…..”

Step 1, the creation step, stores a parsed execution plan.  As some of the parsing work has been done, consequently step 2, the run or execution of the stored procedure, the runtime of the procedure is very much reduced.

While the ASE server has a variety of classifications of stored procedures,  for our talk we will focus on two general types of stored
procedures: system procedures and user procedures. 

System procedures are those procedures that are involved in the ASE system administration.  While SAP Sybase provides these system procedures as part of the base product, we also give you the ability to create these types of special procedures on your own. 

The other type of procedure is the user procedure and this is used for your business logic.  Our template example is a user  procedure template.

There is a third type of stored procedure that warrants a mention simply because in your application programming there might be
a need for this.  The third type of procedure supplied by SAP Sybase is called an extended store procedure (ESP).  An extended stored procedure has the interface of a stored procedure, but instead of containing SQL statements and control-of-flow statements, it executes procedural language code that has been compiled into a dynamic link library (DLL).  The procedural language in which an ESP function is written can be any language capable of calling C language functions and anipulating C datatypes.  ESPs allow  Adaptive Server to perform a task outside the relational database management system (RDBMS), in response to an event occurring within the database.  For example, you could use an ESP to send an e-mail notification or network-wide broadcast in response to an event occurring within the Relational Database Management System (RDBMS).  A simple ESP in windows would be “exec xp_cmdshell ‘dir'” to execute the   “dir” command to list the windows directory.  Too simple I agree, but you get the general idea.

More facts about system and user stored procedures:

• Some system procedures can be run only in a specific database, but many of them can be run in any database
• You can create your own system procedures that can be executed from any database. 
• The rules are to simply create them in the sybsystemprocs database, name them starting with “sp_” and give execute rights to “public.” 
• You can execute stored procedures on remote servers by using the exec <Server>.<database>.<owner>.<procedure> syntax. 
• All Transact-SQL extensions support return values from stored procedures, user-defined return status from stored procedures, and the ability to pass parameters from a procedure to its caller.
• The inputs the the stored procedures an function as both INPUT and OUTPUT source of variables.

The following example of a stored procedure is a favorite one of mine.  Its a simple template that demonstrates:

• modular code; the procedure takes care of itself.
• procedure is aware if it is a main-line code or being called by another program.
• issues a rollback or a commit only at the right time (code efficiency)
• allows errors to be passed up the chain

Note the naming convention; we have chosen to include the database name and at the start of the procedure we have included the drop procedure statement as well as the configuration option that allows us to reference objects such as temporary tables that may have not been created during the running (compile time) of this procedure.  You should also notice that the error checking syncs up nicely into the error checking done in the previous blog on triggers.

— this is the template file and you would save this in a file

— named: databasename_procedurename.txt
— start of the file

— Step 1. Set the environment to avoid any temp tables that are not yet created
use master
go
exec
sp_configure “deferred name resolution”,1
go

— Step 2. Set the local
environment
use <databasename>
go

— Step 3. Delete the one already
there
if exists(select 1 from sysobjects where name = “<databasename_procedurename>” and type = “P”)
drop procedure <databasename_procedurename>
go

–Step 4. the creation
create procedure <databasename_procedurename>
<INPUT VARIBLE 1>,<INPUT VARIBLE N>
as

declare @ret_status int, @tran_flag int
declare @tran_flag = @@trancount

if @@tran_flag = 0
— this procedure is the outermost or calling program 
begin transaction <databasename_procedurename>
else
— this procedure is being called by another    
save transaction <databasename_procedurename>

— start of business processing  

<INSERT BUSINESS RULES HERE!!>

— next code fragment is for error checking and should be after all DML statements
if  (@@error!= 0)
begin
rollback tran <databasename_procedurename>
return
-999
end

exec @ret_status = <NESTED PROCEDURE2:INSERT BUSINESS RULES HERE!!>

— next code fragment is for error checking and should after all nested procedures
if  (@ret_status = -999)
begin
rollback tran <databasename_procedurename>
return -999
end
— end of error checking after calling nested procedure 

— end of business processing 

if @tran_flag = 0
— this is the outermost procedure  
commit <databasename_procedurename>
return
go

— Step 6. Clean up
use master
go
exec sp_configure “deferred name resolution”,0
go

— end of the file

For those of you familiar with stored procedures there are a myriad of options not discussed;  I have not even scratched the surface on such topics as recompile options.  To really get started on the right foot or to have an in-depth look at your existing environment, SAP has several service packages around health checks; check out the Service package named:  Health check service for SAP Sybase server environments.  Using this template as a guide you could start to change your entire procedure scripts to the new template to make your code more modular.  Just because you have written something already doesn’t mean you cannot make it better, more supportable and faster.

Contact myself or the SAP Global Data & Technology Center of Excellence and we would be happy to help.

Help is click away……

Follow the “Database Services Content Library” to access the entire series of Database Services Blogs and join the conversation on Twitter @SAPServices
Learn more about SAP Database Services here
http://www.sap.com/services-and-support/data-and-technology/database-services.epx

Some of the reference materials for this talk were found in the Admin Guide for the ASE Server version 15.7. volume 2