Progress
SQL-92
Guide and Reference


Structure of Stored Procedures

There are two parts to any stored procedure:

EXAMPLE

A simple stored procedure requires the procedure name in the specification, and a statement requiring no parameters in the body. The procedure in this example assumes the existence of a table named HelloWorld, and inserts a quoted string into that table:

CREATE PROCEDURE HelloWorld ()
BEGIN
    SQLIStatement Insert_HelloWorld = new SQLIStatement
    ("INSERT INTO HelloWorld(fld1) values (’Hello World!’)");
END 

EXAMPLE

Subsequently, from SQL Explorer you can execute the procedure like this:

SQLExplorer> CREATE TABLE helloworld (fld1 CHAR(100));
SQLExplorer> CALL HelloWorld();
0 records returned
 
SQLExplorer> SELECT * FROM helloworld;
FLD1

----

 
Hello World!
 
1 record selected 

The procedure specification can also contain other clauses:

EXAMPLE

The following example shows a more complex procedure specification that contains these elements:

CREATE PROCEDURE new_sal (
    IN  deptnum    INTEGER,
    IN  pct_incr   INTEGER
)
RESULT  (
    empname  CHAR (20),
    oldsal  NUMERIC,
    newsal  NUMERIC
)
IMPORT
    import java.dbutils.SequenceType;
 
BEGIN
   .
   .
   .
END 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095