Calling a Stored Procedure from Excel with multiple parameters

Question

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:

Sub GetHeatMapData()

    With ActiveWorkbook.Connections("CARLA-PC-Billing-SP").OLEDBConnection.CommandText = "EXECUTE dbo.GetBillingHeatMap '" & Range("A9").Value & "" & Range("B9").Value & "'"

    End With
    ActiveWorkbook.Connections("CARLA-PC-Billing-SP").Refresh

End Sub

However if I try to run the Stored Procedure from a macro in Excel, one of two things happens:

1. 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.

2. 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.

I am setting NOCOUNT to off at the end of my Stored Procedure. Here are the parameter definitions in the Stored Procedure:

-- Add the parameters for the stored procedure here
    @StartDate Date, 
    @EndDate Date

Here are my connection settings:

My question is why is stored procedure not getting my parameters from the Excel spreadsheet cells and using them to filter the returned data?

Answer 1

1 – expose parametars in excel

2 – define that parameters are entered from excel

3 – OK and refresh.

OK, You don't have to enter parameters in the command text, you can define command text in a way to expect parameters to be passed to the store procedure from excel cell. This is useful especially if your country don’t use US data format. Also, it enables end user to make refresh with some other parameters without need to edit command text. It is simple – in the excel that have connection to sql server in command text after store procedures name you enter ? – as many ? as you have different parameters that your store procedure expects (divided by ,) – like this - execute dbo.your_procedure ?,? After that, you go in the parameters tab (on the same form that you entered command text) and define from witch cell witch parameter is passed to store procedure. Of course, in the stored procedure also need to be specified what parameters are expected:

CREATE procedure [dbo].[your_procedure]

( @DateFrom datetime, @DateTo datetime ) As

--- your store procedure ---

In excel – parameter 1 will be send to the store procedure to a DateFrom parametar.

Answer 2

When you execute a stored procedure, the statement generally looks like:

EXECUTE myProc('param1', 'param2')

The way your command will expand will come out like:

EXECUTE myProc 'param1param2`

Which is nonsense. Try instead:

With ActiveWorkbook.Connections("CARLA-PC-Billing-SP").OLEDBConnection.CommandText = "EXECUTE dbo.GetBillingHeatMap ('" & Range("A9").Value & "','" & Range("B9").Value & "');"

You may still run into issues with the date formatting, so you can handle that in VBA too:

With ActiveWorkbook.Connections("CARLA-PC-Billing-SP").OLEDBConnection.CommandText = "EXECUTE dbo.GetBillingHeatMap ('" & FORMAT(Range("A9").Value, "m/d/yyyy") & "','" & FORMAT(Range("B9").Value, "m/d/yyyy") & "');"

Lastly, I find it good practice to send dynamically generated sql to a variable, and print it to the debug/immediate window before executing so I can catch stuff like that.

sqlStatement = "EXECUTE dbo.GetBillingHeatMap ('" & FORMAT(Range("A9").Value, "m/d/yyyy") & "','" & FORMAT(Range("B9").Value, "m/d/yyyy") & "');"
debug.print sqlStatement
With ActiveWorkbook.Connections("CARLA-PC-Billing-SP").OLEDBConnection.CommandText = sqlStatement

And now you'll have your statement in your immediate window, which you can copy and paste into a SQL client to execute independently of your code.