HOWTO: Determining How to Define a Stored Procedure in ASP

Last reviewed: December 11, 1997
Article ID: Q165156

The information in this article applies to:

Microsoft Active Server Pages, versions 1.0, 1.0b

SUMMARY

This article provides the ASP code necessary to programmatically determine the parameter requirements for any stored procedure. The sample code provided determines the parameter name, data type, direction, and size of all parameters in a stored procedure. The developer can use this acquired information at design time in defining the stored procedure call. The method outlined in this article uses the "Parameters.Refresh" method and the "Parameters" collection and is useful for those developers who do not have access to other tools used for obtaining stored procedure information.

MORE INFORMATION

Determining how to properly call a stored procedure can be tricky if you are unaware of the stored procedure's parameter information. Without the correct information, you can’t properly create the ADO parameters.

The following ASP sample determines any stored procedure's parameter information. In this example we determine the parameter information for parameters in a stored procedure named "sp_MyStoredProc" and place them in an HTML table on the screen.

NOTE: The values reported are numerical. The corresponding constants may be found in the Adovbs.inc file. This file is installed during Active Server Pages setup, and placed in the \aspsamp\samples directory. This directory is normally located in your \inetpub directory. It is recommended programming practice to use the constants versus the numerical values when calling your stored procedure. This makes your code easier to read and maintain.

   <%@ LANGUAGE = VBScript %>
   <!-- #INCLUDE VIRTUAL="/ASPSAMP/SAMPLES/ADOVBS.INC" -->
   <HTML>
   <HEAD><TITLE>Stored Proc Example</TITLE></HEAD>
   <BODY>

   <%
   Set Conn = Server.CreateObject("ADODB.Connection")

   ' The following line must be changed to reflect your data source info
   Conn.Open "data source name", "user id", "password"
   set cmd = Server.CreateObject("ADODB.Command")
   set cmd.ActiveConnection = Conn

   ' Specify the name of the stored procedure you wish to call
    cmd.CommandText = "sp_MyStoredProc"
    cmd.CommandType = adCmdStoredProc

    ' Query the server for what the parameters are
    cmd.Parameters.Refresh
   %>
   <Table Border=1>
   <TR>
      <TD><B>PARAMETER NAME</B></TD>
      <TD><B>DATA-TYPE</B></TD>
      <TD><B>DIRECTION</B></TD>
      <TD><B>DATA-SIZE</B></TD>
   </TR>
   <% For Each param In cmd.Parameters %>
   <TR>
      <TD><%= param.name %></TD>
      <TD><%= param.type %></TD>
      <TD><%= param.direction %></TD>
      <TD><%= param.size %></TD>
   </TR>
   <%
    Next
    Conn.Close
   %>
   </TABLE>
   </BODY>
   </HTML>

When browsed with a web browser, this page might produce a table similar to this:

   PARAMETER NAME   DATA-TYPE DIRECTION DATA-SIZE
   Return_Value     3         4         0
   param1           129       1         30

This would tell you that your stored procedure sp_MyStoredProc has two parameters that need to be defined in the command object. Utilizing this information, you would modify your ASP code to look something like:

   <%
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "data source name", "user id", "password"
    set cmd = Server.CreateObject("ADODB.Command")
    set cmd.ActiveConnection = Conn
    cmd.CommandText = "sp_MyStoredProc"
    cmd.CommandType = adCmdStoredProc

   ' Use the values from the table in the following lines to define
   ' parameters
    cmd.Parameters.Append cmd.CreateParameter("Return_Value", 3, 4)
    cmd.Parameters.Append cmd.CreateParameter("param1", 129, 1, 30)
    cmd.Parameters("param1") = "input value"
    cmd.Execute
   %>

Now look up the numbers just used to define the parameters from your stored procedure in the Adovbs.inc file. You should find the following relevant sections:

   '---- ParameterDirectionEnum Values ----
   Const adParamInput = &H0001
   Const adParamReturnValue = &H0004

   '---- DataTypeEnum Values ----
   Const adInteger = 3
   Const adChar = 129

NOTE: Only the appropriate portions of the Adovbs.inc file are displayed above.

With this information you can change the code to read:

   <%
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "data source name", "user id", "password"
    set cmd = Server.CreateObject("ADODB.Command")
    set cmd.ActiveConnection = Conn
    cmd.CommandText = "sp_MyStoredProc"
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Append cmd.CreateParameter("RETURN_VALUE", _
       adInteger, adParamReturnValue)
    cmd.Parameters.Append cmd.CreateParameter("param1", adChar, _
       adParamInput, 30)
    cmd.Parameters("param1") = "input value"
    cmd.Execute
   %>

Additional query words: datatype data-type
REFERENCES
For the latest Knowledge Base articles and other support information on
Visual InterDev and Active Server Pages, see the following page on the
Microsoft Technical Support site:
http://support.microsoft.com/support/vinterdev/
Keywords : AXSFCompADO AXSFDataBase AXSFSQL kbhowto
Version : 1.0 1.0b
Platform : NT WINDOWS
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 11, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.