Creating stored procedures and functions

Chapter 5 Building Triggers and Procedures

Creating stored procedures and functions

You create stored procedures and functions using the structure of templates defined in the current DBMS.

Inserting script items

When you create or modify the definition for a stored procedure or function, you can insert pre-defined operators, variables, functions and macros into the procedure or function definition, by clicking on the appropriate tool from the Insert Script tool bar, and selecting an item from the menu that appears. The selected script item is inserted at the cursor position in the definition code.

You can use the following tools to insert script items into the procedure or function definition:

Tool	Tooltip	Description
	Operators	Provides logical operators
	Functions	Provides group, number, string, date, conversion and other functions
	Macros	Provides macros to accelerate the creation of a procedure or function definition
	Variables	Provides variables for use with operators and functions
	Edit with SQL Editor	Opens the SQL Editor dialog box. Provides object types and available objects to insert in the procedure or function definition
	SQL/XML Wizard	Opens the SQL/XML Wizard to build a SQL/XML query from a table or a view and insert it in the procedure or function definition
	Insert SQL/XML Macro	Opens a dialog box to select a global element in an XML model open in the workspace with the SQL/XML extended model definition. Inserts a SQL/XML macro referencing the selected element in the procedure or function definition

For more information on macros and variables, see the "Writing SQL Statements in PowerDesigner" appendix.

For more information on the SQL/XML Wizard, see Using the SQL/XML Wizard.

Inserting begin and end scripts

You can specify scripts generated before and after the stored procedure creation statement. This can be done through the Script page of the stored procedure property sheet. You can use the %PROC% and %FUNC% variables in these scripts.

There are several ways to create a stored procedure:

From the physical diagram
From the list of procedures