Generating ADO Parameters with Information Schema Views
By Garth Wells
on 10 December 2001
| 8 Comments
| Tags: Queries
In this article I show how to use an Information Schema View to generate ADO
Parameter code. Even if you don't use ADO, you'll want to read the section that
describes Information Schema Views. If you work with SQL Server long enough, I
am positive you will eventually find a use for them.
A Good Idea
I was reading my favorite ADO Book (ADO Examples and Best Practices, by Bill
Vaughn) the other day and ran across a reference to a VB add-in that
automatically generates the ADO command parameters for a given stored procedure.
I thought the idea was a good one, because creating the ADO code for a
procedure's parameters is a pain. The following shows the code required to
create an ADO parameter for a stored procedure's parameter defined as varchar(30).
As I said, I thought the add-in was a good idea, but I don't use VB. I needed a
SQL Solution that generated code that I could use in my ASP pages.
Information Schema Views
The Information Schema Views are system-supplied views that expose the data
contained in the SQL Server system tables (meta data). System-supplied means
they come with the product and are created during the install process. You can
see the various Information Schema Views by looking in the Views folder of the
master database. To see what each returns look up the topic: Information Schema
Views, in Books Online (BOL).
The Information Schema Views allow the SQL Server Development Team to change
system tables as the product matures, but still present the meta data
information with the same database object. If you have read more than a couple
of books on T-SQL programming, I am sure you have seen warnings that tell you
not to implement code that references system tables. If the system tables change
from one version to the next (there were quite a few changes between 6.5 and
7.0), the offending code will break.
The test table and procedure used to demonstrate the code is listed below.
CREATE TABLE CR (ID tinyint IDENTITY PRIMARY KEY,
CREATE PROCEDURE pr_CR_INSERT
@ReturnValue tinyint OUTPUT
INSERT CR VALUES (@Project,@StartDate)
The purpose of the procedure is to insert a row into the CR table and return the
primary key value to the calling ASP page. This allows you to see that the code
works for both input and output parameters.
The Code-Generating SELECT
The following code is used to generate the ADO parameters for the pr_CR_INSERT
procedure. I use 'cmd1' to designate the ADO command object, but you can easily
change it per your naming convention.
DECLARE @ProcName sysname,
SELECT @CmdName+'.Parameters.Append '+
WHEN 'varchar' THEN 'adVarChar'
WHEN 'char' THEN 'adVarChar'
WHEN 'nvarchar' THEN 'adVarWChar'
WHEN 'int' THEN 'adInteger'
WHEN 'smallint' THEN 'adSmallInt'
WHEN 'tinyint' THEN 'adUnsignedTinyInt'
WHEN 'datetime' THEN 'adDate'
CASE WHEN Parameter_Mode='IN' THEN 'adParamInput,' ELSE 'adParamOutput' END+
CASE WHEN Character_Maximum_Length IS NULL THEN '' ELSE CAST(Character_Maximum_Length AS varchar) END+
CASE WHEN Parameter_Mode='IN' THEN ',request("'+SUBSTRING(Parameter_Name,2,128)+'")' ELSE '' END+')'
The SELECT does nothing more than concatenate string constants with values
returned by the Parameters Information Schema View. If you compare the constants
with the parameter example listed above you will see all the elements are
The Information_Schema.Parameters View returns a stored procedure's (or UDF's)
parameter definitions, so its just a matter of determining what data elements
you need and adding them to the SELECT. You should note that the SELECT does not
include all datatype-->ADO Constant mappings. If you need more you can review
the ADO constants listed here and
cross-reference with the appropriate SQL Server datatype.
For the Record (Refresh Method)
It should be noted that the Parameters object has a Refresh method that creates
the parameters for a stored procedure with a single-line of code. However, it
generates parameters using the notation: Param1..ParamX, which I think produces
code that is slightly more difficult to maintain.