Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 1, 2007

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

By Paul Zikopoulos

Using an SQL/PL stored procedure in your application

Now that you’ve quickly built a DB2 SQL/PL stored procedure, I’ll show you how to quickly incorporate it into your application.

Note: While this article showed you how to build a stored procedure that resides in a DB2 data server running Linux, UNIX, or Windows, you could easily extend this capability to DB2 for i5/OS or DB2 for z/OS using DB2 Connect. The process would be pretty much the same.

1.  Create a new Visual Basic application called CallMySPProject by selecting File>New Project>Windows Application, selecting Visual Basic (which might be located under Other Languages), entering the project name in the Name field, and clicking OK:

Note: Unlike other applications built in this series, this application will be built in Visual Basic.NET.

2.  Ensure that the Add Data Source view is displayed in the IDE by pressing Shift+Alt+Delete or selecting Data>Show Data Sources from the menu bar.

3.  Click Add New Data Source in the Data Sources view, select Database, and click Next:

4.  Select the SAMPLE database from the Which data connection should your application use to connect to the database? drop-down list.

5.  Select the Yes, include sensitive data in the connection string radio button and click Next. (I’m assuming that you’re working on your own test machine here and notice that Visual Studio 2005 collapses the Connection String field automatically).

6.  Accept the defaults on the next page of this wizard. Click Next.

7.  Expand the Stored Procedures toggle, select the SELECTSTAFFBYDEPT stored procedure, and then click Finish:

Note: This step is a little different in previous articles from what I’ve shown below since we are populating a data set from a stored procedure instead of directly from a table.

The designer palette should now look similar to the following one. (You may need to expand the SAMPLEDataSet manually.)

8.  Drag the SELECTSTAFFBYDEPT stored procedure from the Data Sources view and drop it on your WinForm; it should automatically add a data grid and tool strips and look like this:

You can see that two different tool strips were automatically created after you performed this function. (This is different from what I showed you in the Using the FILLBY Method to Filter DB2 9 Data Sets...with a Twist article. Now, this function will be handled by the stored procedure code that you built earlier in this article.

9.  Click on the pull-down menu to the right of the Fill tool strip, and select Label.

Note: If there is no drop-down menu beside the Fill tool strip, click Fill and it should appear.

The toolStripLabel1 (or whatever it is named in your IDE) should now appear in the Fill tool strip:

10.  Right-click the toolStripLabel1 object, select Properties, and then change its Text property to Department Number:

11.  Create a field where you can specify the input parameter for your stored procedure by clicking the Fill drop-down box and selecting Textbox.

12.  Double-click Fill to see all the code that was generated for you:

You can see that the DEPTNUMB variable was automatically dimensioned (DIMed) for you by Visual Studio 2005.

13.  Change the code as follows:

Dim DEPTNUMB As Object = New Object


Dim DEPTNUMB As Object = ToolstripTextBox1.Text

14.  Press F5 to build the application. The application opens:

15.  Enter a valid department number for the STAFF table in the Department Number field, and press Fill. The WinForm passes the parameter value in the Department Number field to the SELECTSTAFFBYDEPT stored procedure and populates the data grid with the results.

You can use any valid parameter for department number in the STAFF table. If you pass the application a department number that doesn’t exist in the STAFF table, it will simply return an empty result set.

The following figure shows the passing of department number 20 to the SELECTSTAFFBYDEPT stored procedure:

Wrapping it up...

In this article, I showed you how to use the SQL/PL designer to create a stored procedure that takes as input a parameter used to represent the DEPT column in the STAFF table. I showed you some of the great design-time enhancement features of the SQL/PL Procedure designer with its IntelliSense hookup to the database schema and the ability to import the input parameter from the table itself (both of which reduce design time and help prevent costly errors). I then showed you how to include your stored procedure in an application.

» See All Articles by Columnist Paul C. Zikopoulos

About the Author

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than ten years of experience with DB2 UDB and has written over one hundred magazine articles and several books about it. Paul has co-authored the books: DB2 9 New Features (available soon), 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 Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë – his new daughter. You can reach him at: paulz_ibm@msn.com.


IBM, DB2, DB2 Connect, DB2 Universal Database, i5/OS, and z/OS are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

Copyright International Business Machines Corporation, 2007. All rights reserved.


The opinions, solutions, and advice in this article are from the author’s experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author’s knowledge at the time of writing.

DB2 Archives