asp存储过程使用大全

　2007-06-25 11:51:48　

1，调用没有参数的存储过程

2，一个输入的参数的存储过程

3，一个输入参数和一个输出的参数

4，一个输入参数，一个输出参数，和一个返回值

如何在ASP中调用SQL存储过程
ASP调用存储过程的技巧
1、最简单的如下
     Dim objConn
     Set objConn = Server.CreateObject(ADOBD.Connection)
     objConn.Open Application(Connection_String)
     Call the stored procedure to increment a counter on the page
     objConn.Execute exec sp_AddHit
    没有参数，没有返回，没有错误处理，就是这个了

    2、带参数的一种调用
    objConn.Execute exec sp_AddHit http://www.aspalliance.com, 1
    请注意分割参数，该方法也不返回记录

    3、返回记录的
     Dim objConn
     Dim objRs
     Set objConn = Server.CreateObject(ADOBD.Connection)
     Set objRs = Server.CreateObject(ADOBD.Recordset)
     objConn.Open Application(Connection_String)
     Call the stored procedure to increment a counter on the page
     objRs.Open objConn, exec sp_ListArticles 1/15/2001
     Loop through recordset and display each article
    4、……
     Dim objConn
     Dim objCmd

    Instantiate objects
    Set objConn = Server.CreateObject(ADODB.Connection)
    set objCmd = Server.CreateObject(ADODB.Command)
    conn.Open Application(ConnectionString)

    With objCmd
     .ActiveConnection = conn You can also just specify a connection string here
     .CommandText = sp_InsertArticle
     .CommandType = adCmdStoredProc Requires the adovbs.inc file or typelib meta tag

     Add Input Parameters
     .Parameters.Append .CreateParameter(@columnist_id, adDouble, adParamInput, , columnist_id)
     .Parameters.Append .CreateParameter(@url, adVarChar, adParamInput, 255, url)
     .Parameters.Append .CreateParameter(@title, adVarChar, adParamInput, 99, url)
     .Parameters.Append .CreateParameter(@description, adLongVarChar, _
     adParamInput, 2147483647, description)

     Add Output Parameters
     .Parameters.Append .CreateParameter(@link_id, adInteger, adParamOutput, , 0)

     Execute the function
     If not returning a recordset, use the adExecuteNoRecords parameter option
     .Execute, , adExecuteNoRecords
     link_id = .Parameters(@link_id)
    End With

    5、存储过程的代码
    Create PROCEDURE dbo.sp_InsertArticle
    (
     @columnist_id int,
     @url varchar(255),
     @title varchar(99),
     @description text
     @link_id int OUTPUT
    )
    AS
    BEGIN
     INSERT INTO dbo.t_link (columnist_id,url,title,description)
     VALUES (@columnist_id,@url,@title,@description)

     SELECT @link_id = @@IDENTITY
    END