Microsoft KB Archive/164485

= How to call SQL Server stored procedures from ASP =

Article ID: 164485

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 1
 * 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

-



This article was previously published under Q164485



SUMMARY


For a Microsoft Visual Basic .NET version of this article, see 306574.

This article demonstrates three methods of calling a stored procedure from Active Server Pages using ActiveX Data Objects (ADO).



MORE INFORMATION
The following example uses the Command object to call a sample stored procedure sp_test. This stored procedure accepts an integer and has a return value of an integer as well: <%@ LANGUAGE="VBSCRIPT" %>  Place Document Title Here  This first method queries the data source about the parameters of the stored procedure. This is the least efficient method of calling a stored procedure. <%  Set cn = Server.CreateObject("ADODB.Connection") Set cmd = Server.CreateObject("ADODB.Command") cn.Open "data source name", "userid", "password" Set cmd.ActiveConnection = cn  cmd.CommandText = "sp_test" cmd.CommandType = adCmdStoredProc ' Ask the server about the parameters for the stored proc cmd.Parameters.Refresh ' Assign a value to the 2nd parameter. ' Index of 0 represents first parameter. cmd.Parameters(1) = 11 cmd.Execute %>  Calling via method 1 ReturnValue = <% Response.Write cmd.Parameters(0) %>

Method 2 declares the stored procedure, and then explicitly declares the parameters. <%  Set cn = Server.CreateObject("ADODB.Connection") cn.Open "data source name", "userid", "password" Set cmd = Server.CreateObject("ADODB.Command") Set cmd.ActiveConnection = cn  cmd.CommandText = "sp_test" cmd.CommandType = adCmdStoredProc cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _     adParamReturnValue) cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _     adParamInput) ' Set value of Param1 of the default collection to 22 cmd("Param1") = 22 cmd.Execute %>  Calling via method 2 ReturnValue = <% Response.Write cmd(0) %>

Method 3 is probably the most formal way of calling a stored procedure. It uses the canocial <%  Set cn = Server.CreateObject("ADODB.Connection") cn.Open "data source name", "userid", "password" Set cmd = Server.CreateObject("ADODB.Command") Set cmd.ActiveConnection = cn  ' Define the stored procedure's inputs and outputs ' Question marks act as placeholders for each parameter for the ' stored procedure cmd.CommandText = "{?=call sp_test(?)}" ' specify parameter info 1 by 1 in the order of the question marks ' specified when we defined the stored procedure cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _  adParamReturnValue) cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _    adParamInput) cmd.Parameters("Param1") = 33 cmd.Execute %>  Calling via method 3<BR> ReturnValue = <% Response.Write cmd("RetVal") %><P> </BODY> </HTML> Note in the above examples, various methods of accessing the Parameters collection of the Command object are used. Some use the default collection of the Command object while others specify what property of a given collection to access.

<div class="references_section">