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:
- Connect to any SQL Server Database
- Optionally filter out System Stored Procs (Prefixed with dt_ or sp_)
- Optionally process only one user-specified procedure
- Assign the Data Type that the Function returns (Long or ADO Recordset)
- Display results so user can cut and paste into VB Class module
Future enhancements to this utility could include the following
- Automatically output results to VB Class Module
- Generate corresponding ASP Class Method execution code
- Automatically determine proper Data Type for Function return value
Page 2: Code the Utility
» 
IE 7
Firefox 2.0
Comment and Contribute
