HOWTO: Determining How to Define a Stored Procedure in ASP

ID: Q165156

The information in this article applies to:

Active Server Pages
ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2
Microsoft Internet Information Server versions 4.0, 5.0

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[ASCII 146]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/

Additional query words:

Keywords : kbsample kbADO kbDatabase kbVisID kbGrpASP kbGrpMDAC kbDSupport kbADO210sp2 kbiis400 kbiis500
Version : WINDOWS:2.0,2.1 SP2; winnt:
Platform : WINDOWS winnt
Issue type : kbhowto