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
(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
- 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)