学步园

 Dim Conn1 As ADODB.Connection
   Dim Cmd1 As ADODB.Command
   Dim Rs1 As ADODB.Recordset


   Dim strTmp As String


   Dim Connect As String
   Dim Drop As String
   Dim Create As String
   Dim sp as string


   Dim i As Integer
   Dim l As Long


   sConnect= "driver={sql server};" & _


             "server=server_name;" & _
             "Database=pubs;UID=uder_id;PWD=password;"


   sCreate = "create proc sp_AdoTest( @InParam int, " & _


             "@OutParam int OUTPUT ) " & _
             "as "  & _
             "select @OutParam = @InParam + 10 " & _
             "SELECT * FROM Authors WHERE "   & _
             "State <> 'CA' "  &amp; _
             "return @OutParam +10"


   sDrop   = "if exists "  &amp; _


             "(select * from sysobjects where "  &amp; _
             "id = object_id('dbo.sp_AdoTest') and " &amp; _
             "sysstat &amp; 0xf = 4)"  &amp; _
             "drop procedure dbo.sp_AdoTest"


   sSP     = "sp_Adotest"


   ' Establish connection.
   Set Conn1 = New ADODB.Connection
   Conn1.ConnectionString = sConnect
   Conn1.Open


   ' Drop procedure, if it exists &amp; recreate it.
   Set Rs1 = Conn1.Execute(sDrop, l, adCmdText)
   Set Rs1 = Nothing


   Set Rs1 = Conn1.Execute(sCreate, l, adCmdText)
   Set Rs1 = Nothing


   ' Open recordset.
   Set Cmd1 = New ADODB.Command
   Cmd1.ActiveConnection = Conn1
   Cmd1.CommandText = "sp_AdoTest"
   Cmd1.CommandType = adCmdStoredProc
   Cmd1.Parameters.Refresh
   Cmd1.Parameters(1).Value = 10
   Set Rs1 = Cmd1.Execute()


   ' Process results from recordset, then close it.
   RS1.Close
   Set Rs1 = Nothing


   ' Get parameters (assumes you have a list box named List1).


   Debug.print vbTab &amp; "RetVal Param = " &amp; Cmd1.Parameters(0).Value
   Debug.print vbTab &amp; "Input  Param = " &amp; Cmd1.Parameters(1).Value
   Debug.print vbTab &amp; "Output Param = " &amp; Cmd1.Parameters(2).Value