title: Execute stored procedure and return resultset
body: <p>I'm a complete VBScript newbie and I am trying to execute a stored procedure and read the resultset. I have tried numerous different approaches using articles online but nothing works and I'm stumped. The database is SQL Server 2008 R2, the application is an off-the-shelf ERP system but I'm able to add my own code to it. </p>
  
  <p>I can execute code by using:</p>
  
  <pre><code>connection.execute"insert into blah blah blah"
  </code></pre>
  
  <p>And I can read result set by using: </p>
  
  <pre><code>Set objRS = CreateObject("ADODB.Recordset")
  
  objRS.Open "select a, b, c FROM blahblah", Connection, adOpenStatic, adLockBatchOptimistic,    adCmdText
  If objRS.EOF = False Then
      a = objRS.Fields("a").Value
      b = objRS.Fields("b").Value
      c = objRS.Fields("c").Value
  End If
  objRS.Close
  </code></pre>
  
  <p>The stored procedure in question is in effect a select statement e.g.:</p>
  
  <pre><code>create procedure [dbname].[dbo].[sptestproc] 
  as 
      @Option1 Varchar(10) = NULL,
      @Option2 Varchar(10) = NULL
  AS
  BEGIN
      select first, second 
      from table1 
      where a = @option1 and b = @toption2
  End
  </code></pre>
  
  <p>My code so far:</p>
  
  <pre><code>Dim sql
  
  sql = "EXEC [dbname].[dbo].[sptestproc] '" &amp; Opt1 &amp; "','" &amp; Opt2 &amp; "'"
  Set RS = CreateObject("ADODB.Recordset")
  RS.Open sql, Connection, adOpenStatic, adLockBatchOptimistic, adCmdText
  Do While Not RS.EOF
      Call App.MessageBox("first",vbInformation,"Data updated")
      Call App.MessageBox("second",vbInformation,"Data updated")
      RS.MoveNext
  Loop
  </code></pre>
  
  <p>But I cannot for the life of me get a procedure to execute and read the results.</p>
  
  <p>Can anyone help?</p>
  
  <p>Thanks</p>