title: Calling a Stored Procedure from Excel with multiple parameters
body: <p>I have set up a connection to my SQL server to the database where the stored procedure is located.  The Stored Procedure works fine in SQLServer.  The Stored Procedure runs from the connection with hard coded parameters in Excel just fine.  I get my dataset and it is inserted into my spreadsheet.  The macro does not work.  In the macro, I am trying to point to a range in the spreadsheet that contains my parameter values so I can type the values into the spreadsheet and then have the macro pick them up and pass them to the stored procedure. I am using date formats for both the spreadsheet values and the parameters in the Stored Procedure. I want the dataset returned to be updated in the spreadsheet with the new data when the macro runs. Here is my Macro:</p>
  
  <pre><code>Sub GetHeatMapData()
  
      With ActiveWorkbook.Connections("CARLA-PC-Billing-SP").OLEDBConnection.CommandText = "EXECUTE dbo.GetBillingHeatMap '" &amp; Range("A9").Value &amp; "" &amp; Range("B9").Value &amp; "'"
  
      End With
      ActiveWorkbook.Connections("CARLA-PC-Billing-SP").Refresh
  
  End Sub
  </code></pre>
  
  <p>However if I try to run the Stored Procedure from a macro in Excel, one of two things happens:</p>
  
  <ol>
  <li><p>If there is an existing dataset in the spreadsheet that was created running the stored procedure from the connection window, then the macro runs without errors but it is not picking up the dynamic variables so the data does not change as it should.</p></li>
  <li><p>If I delete the data set created by running the Stored Procedure from the connection window, select the cell where the data should start, then fire the macro I get a 'subscript out of range' error and nothing happens.  </p></li>
  </ol>
  
  <p>I am setting <code>NOCOUNT</code> to off at the end of my Stored Procedure. Here are the parameter definitions in the Stored Procedure:</p>
  
  <pre><code>-- Add the parameters for the stored procedure here
      @StartDate Date, 
      @EndDate Date
  </code></pre>
  
  <p>Here are my connection settings:
  <a href="https://i.stack.imgur.com/wSHB1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wSHB1.png" alt="enter image description here"></a></p>
  
  <p>My question is why is stored procedure not getting my parameters from the Excel spreadsheet cells and using them to filter the returned data?</p>