Utility to Build VB Class to Match SQL Stored Procedures

What is SP2VB?

SP2VB (Stored Proc To VB Class) is a Visual Basic utility that will create the class module code
necessary to execute SQL Server Stored Procs with an ADO Command object. In addition to preparing
the necessary ADO calls, the utility uses the SQLDMO and SQLNS libraries to query your SQL Server
for the names and parameters of all the Stored Procs in your database.

Why the Need for this Utility?

  • I hate to type!

  • I make mistakes when I type!

  • Typing long lists of parameters is boring!

What more need be said?

Executing a SQL Server Stored Proc from MS Access or from an ASP page is relatively trivial,
but either way, you have to deal with the sometimes long list of input and output parameters.
Even when parameters are few, you still want to keep your code consistent accross your entire
application, employing a common naming scheme and including at least simple error handling.

Boring? Sometimes! But very necessary and always beneficial.

Accordingly, I came up with a plan to automate the task — a simple code generator. The pages
that follow will explain (at relatively high level) what the utility does and how you might
modify the code to meet your specific needs. As usual, the source code is available for
download so that you can test out the utility and adapt it as you please.

Before we get into the code itself, here’s a screen shot of the main form. When we are finished,
the tool will be able to perform the following functions:

  1. Connect to any SQL Server Database

  2. Optionally filter out System Stored Procs (Prefixed with dt_ or sp_)

  3. Optionally process only one user-specified procedure

  4. Assign the Data Type that the Function returns (Long or ADO Recordset)

  5. Display results so user can cut and paste into VB Class module

Future enhancements to this utility could include the following

  1. Automatically output results to VB Class Module

  2. Generate corresponding ASP Class Method execution code

  3. Automatically determine proper Data Type for Function return value

Page 2: Code the Utility

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.
Previous articleTame Those Strings!
Next articleColumn Help

Latest Articles