To Create a Stored Procedure
Create the following command file, INS.RMD, in R:BASE Editor:
*(INS.RMD:)
IF (.p1 > 105) THEN
INSERT INTO contact (custid, contlname) VALUES (.p1, .p2)
RETURN 1
ELSE
RETURN 0
ENDIF
To create the stored procedure from the .RMD file:
PUT INS.RMD AS proc1 p1 INT, p2 TEXT RETURN INTEGER
The following stored procedure example will generate one new row in contact and set v1 = 1.
SET VAR vname = 'Dunn'
SET VAR v1 = (CALL proc1 (106, .vname))
The following stored procedure example will set v1 = 0.
SET VAR vname = 'Dunn'
SET VAR v1 = (CALL proc1 (100, .vname))
To Delete a Stored Procedure
To delete a stored procedure, use the DROP command with the following syntax:
DROP PROCEDURE procname
Optionally, you can enter the following code at the R> Prompt:
SET VAR vProcID = sys_proc_id IN sys_procedures +
WHERE sys_proc_name = 'procname'
DELETE ROWS FROM sys_procedures +
WHERE sys_proc_id = .vProcID
DELETE ROWS FROM sys_proc_mods +
WHERE sys_proc_id = .vProcID
DELETE ROWS FROM sys_proc_cols +
WHERE sys_proc_id = .vProcID
To Rename a Stored Procedure
To rename a stored procedure, use the RENAME command with the following syntax:
RENAME PROCEDURE procname1 TO procname2
To List Stored Procedures
With the LIST command, you can list every stored procedure in a database or list information about a specific stored procedure.
To display the name and a description for every procedure in the open database, use the following syntax:
LIST PROCEDURE
To display a specific procedure and its attributes, use the following syntax:
LIST PROCEDURE procname
This option displays the name, description, ID, date last modified, version, locked by (if locked), and if applicable, the return type and description for the specified stored procedure. If the stored procedure has arguments, the number of arguments and argument names and attributes will be listed.
To Trace a Stored Procedure
With the TRACE command, you can debug the stored procedure command syntax in the R:BASE Trace Debugger.
Enter the following code at the R> Prompt:
TRACE SELECT USING .arg1, .arg2 SELECT sys_proc_src FROM sys_procedures +
WHERE sys_proc_name = 'procname'
