How To Determine Parameter Requirements for a Stored Procedure in ASP

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

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:

Propiedades

Id. de artículo: 165156 - Última revisión: 08/14/2008 - Revisión: 1

Comentarios