大网数据广告活动
Web服务器与PHP

Run SQL Server stored procedures from an ASP Page

· 52CE 我爱测

This article describes how to run SQL Server stored procedures and use parameters from an ASP page.

This article shows how to run SQL Server stored procedures and use parameters from an Active Server Pages (ASP) page.

Original product version:   Internet Information Services
Original KB number:   300488

Summary

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
    )
    AS
    Select @out = count(*) from titles where price < @price
    GO
    

    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: Downloading Northwind and pubs Sample Databases

  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 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 cmd
      Dim ln
      Dim retCount
      
      Set 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 with
      
      Response.Write retcount
      
      Set cmd = Nothing
      %>
      

      [!NOTE] The constants that are used in this sample can be found in the Adovbs.inc 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.

       <%@ LANGUAGE = VBScript %>
       <!-- #INCLUDE VIRTUAL="/ASPSAMP/SAMPLES/ADOVBS.INC" -->
      
  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:

    Command.Parameters.Refresh
    

References

For more information, see Create a simple data application by using ADO.NET.

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 09/24/2020
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。