<%@Language="VBSCRIPT" CodePage="936" LCID="2052"%>
<%Option Explicit%>
<%
'**********************************************
'                     Coding By sjcatsoft                                   |
'                     Date: 2003-1-24                                      |
'**********************************************
%>
<%
    '---- 存储过程的SQL语句

    'CREATE PROCEDURE spTest
    '@param1 char(1),
    '@param2 char(1),
    '@msg varchar(200) OUTPUT
    'AS
    'BEGIN
    '  SELECT * FROM jobs
    '  SELECT @msg = 'this is a test ---' + @param1 + '---' + @param2 + '***'
    'END
    '
    'GO
%>
<%
    '---- CursorTypeEnum Values ----
    Const adOpenForwardOnly = 0
    Const adOpenKeyset = 1
    Const adOpenDynamic = 2
    Const adOpenStatic = 3
   
    '---- LockTypeEnum Values ----
    Const adLockReadOnly = 1
    Const adLockPessimistic = 2
    Const adLockOptimistic = 3
    Const adLockBatchOptimistic = 4

    '---- CursorLocationEnum Values ----
    Const adUseServer = 2
    Const adUseClient = 3
   
    '---- CommandTypeEnum Values ----
    Const adCmdUnknown = &H0008
    Const adCmdText = &H0001
    Const adCmdTable = &H0002
    Const adCmdStoredProc = &H0004
    Const adCmdFile = &H0100
    Const adCmdTableDirect = &H0200
  
    '---- Declare Global Variables
    Dim cn, cmd, rs
   
    '---- Connect DataBase Server
    Set cn = Server.CreateObject("ADODB.Connection")
    cn.Provider = "SQLOLEDB.1"
    cn.CursorLocation = adUseClient '=3
    cn.ConnectionString = "Data Source=(local);User ID=sa;Password=123;Initial Catalog=pubs"
    cn.Open  
   
    '---- 使用ADODB.COMMAND的方式
    Sub By_AdoCmd
      
        Set cmd = Server.CreateObject("ADODB.Command")
        cmd.ActiveConnection = cn
        cmd.CommandType = adCmdStoredProc '=4
        cmd.CommandText = "spTest"
        cmd.Parameters.Refresh '---- 此条语句是关键，自动获取 Parameters 列表,省去自己创建parameter诸多问题，自己创建容易出现字段类型不匹配的错误
        cmd.Parameters(1) = "a"
        cmd.Parameters(2) = "b"
        cmd.Execute
        response.Write(cmd.Parameters(0))
        response.Write("<br/>")
        response.Write(cmd.Parameters("@RETURN_VALUE")) '---- @RETURN_VALUE/0 是 SQL SERVER 2000 默认的返回参数，如果执行成功，则返回 0，此值可以自定义返回
        response.Write("<br/>")
        response.Write(cmd.Parameters(3))
        response.Write("<br/>")
        response.Write(cmd.Parameters("@msg"))

        response.Write("<hr>")
        Set rs = cmd.Execute '---- 如果存储过程只返回 OUTPUT 参数值， 此句不能使用
        response.Write(cmd.Parameters(0))
        response.Write("<br/>")
        response.Write(cmd.Parameters("@RETURN_VALUE"))
        response.Write("<br/>")
        response.Write(cmd(3))
        response.Write("<br/>")
        response.Write(rs.RecordCount)
        response.Write("<br/>")
        response.Write(rs.Fields.Count)
       
        rs.Close
        Set rs = nothing
        Set cmd = nothing
        response.Write("<hr>")
       
    End Sub
   
    '---- 使用ADODB.RECORDSET 方式
    Sub By_Recordset
      
        Set rs = Server.CreateObject("ADODB.Recordset")
        rs.Open "DECLARE @msg VARCHAR(200);EXEC spTest 'a','b',@msg OUTPUT;SELECT @msg", cn, adOpenKeyset, adLockReadOnly, 1
       
        'Set rs = cn.Execute("DECLARE @msg varchar(200);EXEC spTest 'a','b',@msg OUTPUT;SELECT @msg") '和上面结果相同，只是方式的变化
       
        Dim fl
        For each fl In rs.Fields
            response.Write(fl.Name)
            response.Write("<br/>")
        Next
       
        Do While Not rs.Eof
            response.Write(rs.Fields(0).Value)
            response.Write("<br/>")
            rs.MoveNext
        Loop
      
        response.Write(rs.RecordCount)
        response.Write("<br/>")
        response.Write(rs.Fields.Count)
        response.Write("<br/>")
        Set rs = rs.NextRecordset
        response.Write(rs.Fields.Count)
        response.Write("<br/>")
        response.Write(rs.Fields(0).Value)
        response.Write("<br/>")
       
        rs.Close
        Set rs = nothing
        response.Write("<hr>")
       
    End Sub
   
    Call By_AdoCmd
    Call By_Recordset
    cn.Close
    Set cn = nothing
    response.End
%>