Generating ADO Parameters with Information Schema Views

By Garth Wells on 10 December 2001 | 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).

cmd1.Parameters.Append cmd1.CreateParameter("Project",adVarChar,adParamInput,30,request("Project"))

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.

Test DDL

The test table and procedure used to demonstrate the code is listed below.

CREATE TABLE CR (ID tinyint IDENTITY PRIMARY KEY,
                 Project varchar(30),
					  StartDate datetime)
go
CREATE PROCEDURE pr_CR_INSERT
@Project varchar(30),
@StartDate datetime,
@ReturnValue tinyint OUTPUT
AS
INSERT CR VALUES (@Project,@StartDate)
SET @ReturnValue=@@IDENTITY
go

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,
        @CmdName varchar(30)
SET @ProcName='pr_CR_INSERT'
SET @CmdName='cmd1'


SELECT @CmdName+'.Parameters.Append '+
       @CmdName+'.CreateParameter("'+SUBSTRING(Parameter_Name,2,128)+'",'+
       CASE Data_Type
        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'
        ELSE 'AdVarchar'
       END+','+
       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+')'
FROM Information_Schema.Parameters
WHERE Specific_Name=@ProcName

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 present.

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.

Garth
www.SQLBook.com