Stored procedure

Introduction

A stored procedure can be seen as a number of T-SQL statements, grouped together into a block of code (See also http://en.wikipedia.org/wiki/Stored_procedure).

Within a stored procedure you can not only retrieve results, or modify data in a table, but also use normal control-flow type of statements such as “if” and “while”. Using stored procedures can give you the following benefits when compared to plain SQL statements:

• Stored procedures are stored in the database and the syntax is already checked.
• Once executed, the resulting query plan will stay in memory (as long as possible) for future use. This eliminates the need for recalculating the query plan the next time the procedure is executed.
• Modularity, there is no need to recode the same SQL statements into the application over and over again. Just put the logic into a stored procedure and call that procedure from the application.
• Reduced network traffic.
• More fine grained security rules can be implemented.

Creating a stored procedure

The following example creates a stored procedure. It will retrieve the title from a table called titles when a valid title_id is supplied. The output will be returned in an output-variable. When the title can not be determined, an error message is raised.

create procedure getTitle @title_id tid,
                          @title    varchar(80) output
as

-- declare some local variables
declare @error  int,
        @rowc   int

-- retrieve the title
select @title = title
       from   titles
       where  title_id = @title_id

select @error = @@error,
       @rowc  = @@rowcount

-- check for errors (permissions or so)
if     @error != 0
       return  1

-- do we have a valid title?
if     @rowc   != 1
begin
       raiserror 20000 "Title for id %1! not found", @title_id
       return  1
end

return 0

Executing the stored procedure

declare @title varchar(80)
exec getTitle "BU1032", @title output

Sample output:

(return status = 0)

Return parameters:

                                                                                 
-------------------------------------------------------------------------------- 
The Busy Executive's Database Guide


(1 row affected)

Remove a stored procedure from the database

When you need to modify a stored procedure it must be dropped first and then be re-created.

drop proc getTitle

Notes

• In your ASE server there are also stored procedures from Sybase. The names of these generally start with sp_ and are located in the sybsystemprocs database. You should create your own stored procedures not in Sybase system databases, like master or sybsystemprocs.
• Statistics of tables should be up-to-date so ASE can calculate good query plans. Run “update index statistics” regularly, in combination with sp_recompile.
• Increase the size of the procedure cache when needed.
• Remember to use transaction handling. Stored procedures are not a separate logical unit of work.
• Use good error handling, see the above example.
• Within a stored procedure, you can call another stored procedure or do all other kinds of things. Check the Sybase documentation for more details (http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug666.htm)