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

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:

To perform the steps in this
article, ensure that you’ve created the SAMPLE database by entering the db2sampl
command from your operating system’s command prompt. In addition, ensure that
you’ve added a connection to this database in the Server Explorer view.

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:

a.  Right-click
the SELECTSTAFFBYDEPT stored procedure and select Run. The Run
Options window opens.

b.  Deselect
the Null check box.

c. Click
Value and enter 20.

d.  Click
Run.

These steps are shown
below:

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.

Paul Zikopoulos
Paul Zikopoulos
Paul C. Zikopoulos, BA, MBA is the Program Director for the DB2 Evangelist team at IBM. He is an award-winning writer and speaker with more than 14 years of experience with DB2. Paul has written more than 230 magazine articles and 11 books on DB2 including, Information on Demand: Introduction to DB2 9.5 New Features, DB2 9 Database Administration Certification Guide and Reference (6th Edition), DB2 9: New Features, Information on Demand: Introduction to DB2 9 New Features, Off to the Races with Apache Derby, DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Clusters) and a DB2 Certified Solutions Expert (BI and DBA). In his spare time, he enjoys all sorts of sporting activities, including running with his dog Chachi, avoiding punches in his MMA training, and trying to figure out the world according to Chloë - his daughter.

Latest Articles