Home

ASP ADVANCED

You may discover that you need to use a stored procedure, but don't know the parameters that the procedure requires. For example, you may not know the datatypes of the parameters or their sizes. How can you determine this information?

You can retrieve information about the parameters used in a stored procedure by using the following script:

<%
Set MyConn=Server.CreateObject("ADODB.Connection")
Set MyCommand=Server.CreateObject("ADOOB.Command")
MyConn.Open "FILEDSN=d:\Program Files\Common Files\ODBC\Data Sources\MyData.dsn"
Set MyCommand.ActiveConnection=MyConn
MyCommand.CommandType=adCMDStoredProc
MyCommand.CommandText="sp_myproc"
MyCommand.Parameters.Refresh
%>
<HTML>
<HEAD><TITLE>Parameter Information</TITLE></HEAD>
<BODY>
<TABLE BORDER=1>
<CAPTION>Parameter Information</CAPTION>
<TR>
<TH>Parameter Name</TH>
<TH>Datatype</TH>
<TH>Direction</TH>
<TH>Size</TH>
</TR>
<%For Each thing in MyCommand.Parameters %>
<TR>
<TD><%=thing.name %></TD>
<TD><%=thing.type %></TD>
<TD><%=thing.direction %></TD>
<TD><%=thing.size %></TD>
</TR>
<%
Next
MyConn.Close
%>
</TABLE>
</BODY>
</HTML>

This example displays all the parameter information for the procedure named sp_myproc. The name, datatype, direction, and size for each parameter is displayed in a table. (The direction indicates whether the parameter is an input parameter, output parameter, or return status value.) To display information about another stored procedure, simply substitute the procedure's name for sp_myproc.

The important statement in this example is MyCommand.Parameters.Refresh. When this statement is executed, information about the stored procedure's parameters is retrieved from the database.

This script doesn't return constants. instead, it returns raw values. To interpret the values returned by this script, you need to examine the ADOVBS indude file. In this file, the raw numerical values are associated with the correct constants.