How To Determine Parameter Requirements for a Stored Procedure in ASP

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 cannot 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
   %>