HOWTO: Calling SQL Server Stored Procedures from ASP

Last reviewed: January 26, 1998
Article ID: Q164485

The information in this article applies to:

Microsoft ActiveX Server, version 1.0

SUMMARY

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" %>
   <!--#include virtual="/ASPSAMP/SAMPLES/ADOVBS.INC"-->
   <HTML>
   <HEAD><TITLE>Place Document Title Here</TITLE></HEAD>

   <BODY>
   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.<BR>
   <%
   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<BR>
   ReturnValue = <% Response.Write cmd.Parameters(0) %><P>

   <!-- ************************************************************ -->

   Method 2 declares the stored procedure, and then explicitly declares
   the parameters.<BR>
   <%
   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<BR>
   ReturnValue = <% Response.Write cmd(0) %><P>

   <!-- ************************************************************ -->

   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.

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:

   http://support.microsoft.com/support/vinterdev/

Keywords          : AXSFDataBase kbprg
Version           : 1.0
Platform          : WINDOWS
Issue type        : kbhowto
Solution Type     : kbcode

================================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: January 26, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.