Creating SQL and SPL Procedures

The IBM^® Database Add-Ins for Visual Studio simplify the process of creating and testing procedures. You can use one of the following tools to create an SQL or SPL procedure:

The IBM Procedure Designer, with which you can generate an SQL procedure and save it to an IBM database that has a data connection in the Server Explorer.
The Visual Studio Code Editor, with which you can manually insert the SQL statement in a procedure script file in an IBM database project in the Solution Explorer.

With the designer, you name the procedure, define the structure, and specify the parameters and additional options. You also can customize the generated source code by specifying files that contain specific code fragments.

Important: If you use Visual Studio 2008, do not create stored procedures with names that are longer than 39 characters. Stored procedures with names that are longer than 39 characters can cause errors when you use the Visual Studio 2008 debugger and other components.

For SQL procedures, you can specify and include the following code fragments: header, variables declaration, exception handlers, and statements that precede the END statement.

With the designer, you also can specify access privileges for the procedure.

Creating overloaded procedures that are based on data type produces undefined results.

To create an SQL or SPL procedure by using the designer:

In the Server Explorer, right-click Procedures under your data connection, and then select Add New SQL Procedure with Designer on the shortcut menu.
The IBM Procedure Designer opens.
Use the views in the designer to define the procedure.
For detailed information about the fields in the designer views, see IBM Procedure Designer.
Save the procedure definition. From the File menu, select Save [procedure name].
The procedure definition script is run against the database and the procedure is created.
Close the designer.

To create an SQL or SOL procedure by using the editor:

In the Solution Explorer, right-click the Procedures folder under your IBM database project, select Add, and then select New Item on the shortcut menu.
The Add New Item window opens.
In the Categories list, select the Procedures folder.
In the Templates list, select SQL Procedure.
In the Name field, type a name for the new procedure file, and then click Add.
A new procedure file with a basic structure is created and added to your project.
Use the editor to make changes to the procedure.
To save your changes, perform one of the following actions:
- To save the procedure with its default file name, from the File menu, select Save [file name].
- To save the procedure with another name, from the File menu, select Save [file name] As, type a new name for the procedure file in the Save File As window, and then click Save.

See Also