Building and Using an SQL/PL Stored Procedure with Visual Studio 2005

March 1, 2007

Since the middle of 2006, I’ve been writing about all the great integration features in IBM DB2 9 for the Microsoft Visual Studio 2005 integrated development environment (IDE) and the accompanying ADO.NET 2.0 API.

In an article called Building Tables with the DB2 Table Designer and Visual Studio 2005 I showed you how to build a table using a feature unique to DB2 9 called a designer. As you may recall, I mentioned that there are designers to build and create all sorts of schema objects such as views and stored procedures, as well as non-schema objects such as scripts and more. In this article, I want to show you how to create a stored procedure using the SQL/PL Stored Procedure designer and show you how you can leverage that stored procedure in a simple WinForm application.

Note:

Building an SQL/PL stored procedure using a designer

To build an SQL/PL stored procedure that selects the employees of a specific department based on using the DB2 9 SQL/PL Stored Procedure designer in Visual Studio 2005, perform the following steps:

1.  Expand the entry for the SAMPLE database in the Server Explorer, select the Procedures folder, and then Add New SQL Procedure with Designer

2.  The Procedure designer opens. Call this procedure SELECTSTAFFBYDEPT in the Procedure identification section of this designer. Optionally, specify the schema name under which this routine will be created and a comment describing it.

Since I don’t show you the rich debug integration between Visual Studio 2005 and the IBM add-ins for Visual Studio 2005, you can leave the Debug mode setting as is, or set it to Allow if you want to use the integrated debugging capabilities of this IDE for this routine.

3.  In the Procedure Parameters window, add a parameter to this routine by clicking Add parameter ().

4.  Click the Name field and rename this parameter to DEPTNUMB.

5.  Click Import Parameters () to discover the data type of the parameter you want to pass to the routine that retrieves employees within the STAFF table based on the DEPT column.

6.  The Import window opens. Optionally, use Schema name to filter the tables in this window, find the STAFF table, select the DEPT column, move it to the Selected list by clicking Add column to selected list (), and click OK.

The IBM add-ins for Visual Studio 2005 give you some unique features that enhance the rapid application development (RAD) experience of the Visual Studio 2005 IDE. The ability to discover parameter data types is one of them. This prevents you from choosing a wrong data type for the input parameter to be used by the stored procedure you are building, which in turn reduces potential coding errors.

There are a host of other unique DB2 9 RAD features for Visual Studio 2005, some of which I covered in previous articles. These features include column discovery for table creation, IntelliSense hookup to the underlying DB2 schema (which I’ll show you later in this article), a specific IBM Output Message Pane that details specific DB2 data server activity and error messages, and more.

The Procedure Parameters portion of this designer should now look similar to this:

7.  Erase the DEPTNUMB parameter by selecting it and clicking Delete Parameter (). Rename the DEPT parameter to DEPTNUMB.

Obviously, you can choose to specify or discover the correct parameter immediately; I took you through these extra steps to illustrate this designer’s multiple features.

8.  Collapse the Procedure Identification and Procedure Parameters sections of this designer by clicking their associated toggle () icons. The designer should now look like this:

9.  Replace

select ROUTINENAME, ROUTINESCHEMA from SYSIBM.SYSROUTINES;

with

select * from STAFF where dept = deptnumb;

Note: When you enter this new statement, you can leverage the IntelliSense hookup to the data server’s schema by specifying the schema name before the STAFF table, and the schema and table names before the DEPT column, as shown below:

10.  Build this stored procedure by clicking Ctrl+S, and then Yes.

The many other features of the SQL/PL Stored Procedure designer are outside the scope of this article. I recommend that you experiment by building other routines to learn all about the capabilities of this wonderful and unique tool.

11.  Refresh the Procedures folder’s view in the Server Explorer by right-clicking this folder, and selecting Refresh if you need to.

12.  Test your stored procedure by passing it a value of 20 to the DEPTNUMB parameter as follows:

Notice that when you run a procedure using the IBM add-ins for Visual Studio 2005, you can leverage options for pre- and post-run script processing. For example, you may want a pre-run script to create and populate a table with data, while a post-run script may delete the table. A Commit after run script may check the routine’s code into Visual Source safe, and so on. In addition, the next time you run this routine, the stored procedure will cache the entered parameter’s value to make it easier to run.

Your results are returned in a DB2 data grid and should be similar to this:

The DB2 data grid in Visual Studio 2005 is feature-rich and capable of many operations such as import and export of data (including XML), projections, restrictions, row-level filtering, sorting, and more.