Stored Procedures

Advantage Concepts

A stored procedure is a block of code, either an SQL script or a compiled library, which is executed on the server via explicit calls using ‘EXECUTE PROCEDURE’ statements.

Why Use a Stored Procedure?

Stored Procedure Containers

Stored procedures can be written as SQL scripts, WIN32 DLLs, COM Objects, Linux shared objects, or .NET Assemblies.

SQL scripts are the recommended container type. Stored procedures written as other libraries are called Advantage Extended Procedures (AEPs). See the Advantage Extended Procedures topic for more details on AEPs.

See the SQL scripts documentation for a full discussion of the Advantage scripting language.

Creating a Stored Procedure

Stored procedures can be created via the CREATE PROCEDURE statement or visually using the Advantage Data Architect (which is the recommended method). A data dictionary is required to use stored procedures.

When creating a new stored procedure, you define the input and output parameters the procedure will accept. These parameters can be any data type supported by the ADT table format.

The input and output parameters are passed to the stored procedure via virtual table references with one column per parameter. For example, if you define a new stored procedure with two input parameters, X and Y, then inside your procedure you can access these values by referencing the X any Y columns in a virtual table called __input:

DECLARE localX Integer, localY Integer;

DECLARE input AS SELECT * FROM __input;

localX = input.X;

localY = input.Y;

 

Similarly, output parameters can be returned by setting their values in a virtual table called __output:

INSERT INTO __output VALUES ( ‘output param one’, ‘output param two’ );

 

The virtual table __output is not limited to one row. Your procedure can return multiple rows, effectively returning a multi-row dataset to the caller, which the caller can manipulate just like any other result set. It should be noted that this result set is returned as a static cursor.

The column structure of the __input and __output tables directly reflects the parameter names and data types you specify when creating the procedure in the data dictionary.

Calling a Stored Procedure

Stored procedures are called by executing EXECUTE PROCEDURE statements via any SQL interface. For example, to call a procedure with two integer parameters you would use the following statement:

EXECUTE PROCEDURE MyProcedureName( 6, 9 )

 

The same procedure could be called using named parameters:

EXECUTE PROCEDURE MyProcedureName( :param1, :param2 )

 

Stored Procedure Privileges

User privileges are not checked during stored procedure execution. This functionality, among other things, lets you hide tables from users, but allow access and modifications to those tables only through stored procedures which you control.

Stored Procedures and Transactions

All operations performed inside of a stored procedure are included in the context of existing transactions. If you roll back a transaction after calling a stored procedure, all data operations performed by that stored procedure will also be rolled back.