Progress
SQL-92
Guide and Reference


Writing Stored Procedures

Use any text editor to write the CREATE PROCEDURE statement and save the source text as a text file. That way, you can easily modify the source text and try again if it generates syntax or Java compilation errors.

EXAMPLE

From the command prompt, you can invoke SQL Explorer and submit the file containing the CREATE PROCEDURE statement as an input script, as shown in the following example:

$ sqlexp -infile hello_world_script.sql example_db 

From the command prompt, you can invoke SQL Explorer and submit the file containing the CREATE PROCEDURE statement as an input script, as shown in the following example:

-- File name: hello_world_script.sql
-- Purpose: Illustrate a CREATE PROCEDURE statement.
@echo true;
@autocommit true;
CREATE PROCEDURE HelloWorld ()
 
BEGIN
     SQLIStatement Insert_HelloWorld = new SQLIStatement (
     "INSERT INTO HelloWorld(fld1) values (’Hello World!’)");
     Insert_HelloWorld.execute();
END
;
COMMIT WORK; 

The Java snippet within the CREATE PROCEDURE statement does not execute as a standalone program. Instead, it executes in the context of an application call to the method of the class created by the SQL server. This characteristic has the following implications:


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