Microsoft KB Archive/165156

= How To Determine Parameter Requirements for a Stored Procedure in ASP =

Article ID: 165156

Article Last Modified on 5/2/2006

-

APPLIES TO


 * Microsoft Active Server Pages 4.0
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Internet Information Server 4.0
 * Microsoft Internet Information Services 5.0
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Remote Data Service for ADO 2.6
 * Microsoft Remote Data Services 2.0

-



This article was previously published under Q165156



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 %>  Stored Proc Example 

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

