A stored procedure is a group of SQL statements and optional control-of-flow (if,
else,
while
statements, and so on) that form a logical unit and perform a particular task. Stored procedures encapsulate a set of operations or queries to execute on a database server.
Prerequisites To create an Adaptive Server Enterprise stored procedure skeleton using Database Development, establish an Adaptive Server Enterprise connection profile.
- In the Databases folder in Enterprise Explorer, under the database connection profile,
expand
the
tree for a database, expand the
Schemas folder and then the tree for the appropriate schema owner.
- Right-click the
Stored Procedures folder, and choose
.
-
Enter the
Name for the stored procedure.
Do not use spaces in Adaptive Server Enterprise procedural object names. Select
Recompile to recompile the stored procedure every time it is invoked.
- (Optional) Enter a description in the
Comments field.
-
Click
Next to add parameters.
-
Enter a parameter Name.
-
Select the
Datatype.
-
Select the
IN/OUT option.
-
(Optional) Specify the default value for the parameter you are defining.
-
Click
Next to declare variables.
-
Enter the
Name for any local variables that you want to declare.
-
Select the
Datatype.
- Click
Next and specify save and execution
properties.
| Field |
Description |
|---|
| Generate Script |
Generate a SQL file for the object. |
| Enter or Select the Parent Folder |
Specify an existing project or create a new project in
which to save the SQL file. |
| File Name |
Enter the file name in which to store the
SQL code. |
| Execute Immediately |
Execute the SQL code immediately. If you do not select this
option, you must complete execution of the SQL file later to create the object. |
| Open in Editor After Creation |
Create the object and open it in its associated editor. |
- Click
Finish to create the stored procedure.