You are currently offline, waiting for your internet to reconnect

How to run SQL Server stored procedures from an ASP Page

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q300488
This article describes how to run SQL Server stored procedures and use parameters from an Active Server Pages (ASP) page.

This article assumes that you are familiar with the procedure to use ActiveX Data Objects (ADO) in an ASP page.

Step-by-Step Example

  1. Run the following query in SQL Server Query Analyzer or SQL Server Management Studio against the Pubs database:
    CREATE proc MyProc(	@price smallint,	@out smallint OUTPUT)ASSelect @out = count(*) from titles where price < @priceGO						
    The stored procedure ("MyProc") takes one input parameter ("@price") and returns one output parameter ("@out").

    Note By default, the Northwind sample database and the pubs sample databases are not installed in SQL Server 2005. These databases can be downloaded from the Microsoft Download Center. For more information about how to download the Northwind sample database and the pubs sample database, visit the following Microsoft Web site:
  2. The following ASP sample code calls the newly created stored procedure. You can use this ASP code to set up the input parameter and run the query.
    1. Use the CreateParameter method to create parameters in Microsoft ActiveX Data Objects (ADO) as follows:
      Set myParameter = Command.CreateParameter (Name, [Type], [Direction], [Size], [Value])						
    2. Appended the parameter to the Parameters collection as follows:
      Command.Parameters.Append myParameter							
      NOTE: The parameters in the Parameters collection must match the order of the parameters in the stored procedure.
    3. Run the command to pass parameter values in and out of the stored procedure as follows:
      <%Dim cmdDim lnDim retCountSet cmd = Server.CreateObject("ADODB.Command")	With cmd    .ActiveConnection = "Paste your connection string here"    .Commandtext = "MyProc"    .CommandType = adCmdStoredProc    .Parameters.Append .CreateParameter("@price", adSmallInt, adParamInput, 10)    .Parameters("@price") = 22    .Parameters.Append .CreateParameter("@retValue", adSmallInt, adParamOutput, 10)    .Execute ln, , adExecuteNoRecords    retCount = .Parameters("@retValue")End withResponse.Write retcountSet cmd = Nothing%>						
    NOTE: The constants that are used in this sample can be found in the file. This file is installed during Active Server Pages setup and placed in the \Aspsamp\Samples folder, which is normally located in your \Inetpub folder. It is recommended programming practice to use the constants rather than the numerical values when you call your stored procedure so that your code is easier to read and maintain.
  3. Modify the ADO connection string as appropriate for your environment.
  4. Save the ASP page, and view it in the browser.

    NOTE: It can be difficult to determine how to properly call a stored procedure if you are unaware of the stored procedure's parameter information. Without the correct information, you cannot properly create the ADO parameters. You can use the Refresh method of the Parameter object to populate the Parameters collection automatically, based on the stored procedure's definition on the server. For example:
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
306574 How To Call SQL Server Stored Procedures in ASP.NET by Using Visual Basic .NET
For additional information about the Refresh method, click the article number below to view the article in the Microsoft Knowledge Base:
165156 How To Determine Parameter Requirements for a Stored Procedure in ASP
For additional information about how to use a data link file (.udl) to create a connection string, click the article number below to view the article in the Microsoft Knowledge Base:
189680 How To Use Data Link Files with ADO
For additional information about retrieving recordsets from Oracle, click the article number below to view the article in the Microsoft Knowledge Base:
229919 How To Retrieve a Recordset from an Oracle Stored Procedure Using ADO in ASP
execute ASP SQL Server parameter

Article ID: 300488 - Last Review: 12/14/2005 00:49:31 - Revision: 5.2

Microsoft Active Server Pages 4.0, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbfaq kbhowto KB300488