Stored Procedures

About Stored Procedures

Up to now, we have submitted batches of SQL statements interactively.

It is possible to take a batch of SQL statements and put them in a "procedure" stored in a Sybase server database. This is called a "stored procedure".

The command to declare a stored procedure is:

create proc {procedure name} as {statement or block of statements}

For example:

create proc myproc as begin select au_id, au_lname from authors where state="CA" select title_id, price from titles where price > $20.00 return 0 end

Stored Procedure with Parameters

Parameters permit stored procedures to be used in a flexible fashion.

Parameters are defined as part of the stored procedure creation statement.

The syntax of a "create proc" command with parameters is:

create proc {proc name} ( @{param_name} {param_type}, @{param_name} {param_type}, {...} ) as {statement} For example: create proc myproc ( @myprice money, @myadvance money ) as begin select title_id from titles where price > @myprice and advance > @myadvance end

How to Invoke Stored Procedures

Syntax:

exec [database.owner.]{procname} {opt params} Where "database" and "owner" will default to current database and dbo (database owner, we'll talk about this later).

Example:

exec myproc $20.00, $1000.00

In this example, the user would see the same results as if he typed in:

select title_id from titles where price > $20.00 and advance > $1000.00

The "exec" is not necessary if the stored procedure call is the 1st line in a batch.

Stored Procedure Return Status

Stored procedures can have return status.

A "return" statement returns from the stored procedure with an optional status parameter.

The return status is zero for success, negative otherwise. Negative values between -1 and -99 are reserved.

Example:

create myproc ( @myprice money ) as begin select title_id from titles where price > @myprice if @@rowcount = 0 return -999 else return 0 end

To invoke a stored procedure with return status:

declare @mystatus int exec @mystatus = myproc $200000.00 if @mystatus != 0 begin {do error processing} end else begin {do normal processing} end

Other Stored Procedure Parameter Stuff

Stored procedure parameters can have default values:

create proc myproc ( @myparam1 int = 0, @myparam2 int = null, @myparam3 char(20) = "mydefault" ) as ... exec myproc 1 /* params 2 & 3 default */

Stored procedures can have output parameters:

create proc myproc ( @myparam1 money, @myoutparam int output ) as select @myoutparam=count(*) from titles where price } @param1

Stored procedure output parameters are significant with remote procedure calls (later in the class).

Stored Procedure Execution Plans

One of the benefits of stored procedures is that the SQL in the stored procedure is pre-compiled in an internal format which can be directly executed by the Sybase server.

The first time a stored procedure is invoked, is automatically compiled. As part of this compilation, a query execution plan is generated. The query execution plan describes the order in which tables are to be accessed and the indexes to be used.

The query execution plan is optimized for the stored procedure parameters and data in database tables at the time the stored procedure is first executed.

One common problem occurs when stored procedures are first executed when the tables they will be accessing are largely empty. The query plans will generally prefer to scan the tables instead of using indexes. As the tables fill with data, performance can significantly degrade.

The "sp_recompile" command can be used to fix this problem:

sp_recompile {table_name}

This will set a flag in each stored procedure that needs to be recompiled (i.e, that references the specified table). The next time one of these stored procedures is invoked, it will automatically be recompiled.

Recompiling is very quick (fractions of a second).

When & Where to Use Stored Procedures

Stored Proc Performance

Stored procedures are stored in the server in a precompiled format. If many users need to invoke the basically the same query just with different parameters, it makes sense to put the query into a stored procedure.

Transact-SQL that is kept in a stored procedure does not have to be parsed and compiled each time it is invoked.

It is possible to implement algorithms that could run in either the client or in the server as a stored procedure. There are multiple criteria which decide where the code should go.

In the case of algorithms which need high bandwidth access to database tables, it makes sense to put them into stored procedures.

Sybase "temp" tables can often be used in these situations. A stored procedure can implement a series of queries each based on a temporary table from the previous step. The data in the application database tables and the data in the temp tables are all local to the stored procedure.

Also, a stored procedure uses CPU cycles on the server box, not the client box. This can be good and bad, depending on the specifics of your client and server system.

The computer where the Sybase server is running will typically have a much faster CPU than the client. If the Sybase server box is not heavily loaded, it makes sense to implement the algorithm in a stored procedure. On the other hand, if a large number of clients do this, the server will be flooded.

Appropriate Functionality

Typical functional areas of a client/server architecture are:

Code which is related to screen manipulation is more appropriately put into the front end client program.

Code which is related to the application logic can be part of the client or part of the server (as a stored procedure). It depends on it's closeness to either the user interface or to the data access.

Transaction logic fits nicely into stored procedures and as we'll see in the next chapter, triggers.

Code Sharing

A stored procedure can remove complex SQL from the front end client application.

This is an easy way to share a complex query between multiple applications. Beware of over sharing.

Another deciding factor in where to put the code is the relative sophistication of Transact-SQL compared to the language being used in the client program. Also, which language the project developers are more familiar with.

Transact-SQL has limitations when compared to C. Transact- SQL does not have any complex variables (ie. data structures). It is difficult to write Transact-SQL that will perform a series of Transact-SQL statements for each row in a table.

Stored procedures can call other stored procedures. This permits code modularity. Modularity is limited, however, by the global nature of the database tables being accessed.

As we'll see later stored procedures are useful from a security point of view. It is possible to give users access to a table only through a stored procedure. In this way, stored procedures can be used like views to provide a logical view of the data (using security to enforce that view).

Next Chapter