Running a SQL Stored Procedure from Excel with Dynamic Parameters

In my life-changing post, Running a SQL-Stored Procedure from Excel, I showed you how to make Excel interact with a SQL server Stored Procedure.


Since that post, I’ve had several requests to cover the topic of passing dynamic parameters to a Stored Procedure. So today, I will FINALLY show you.


Step 1: Data tab – > From Other Sources -> From SQL Server


Step 2: Enter Credentials.


Step 3: Choose any small table or view. It doesn’t matter which one because we’ll be changing the connection anyway.


Step 4: In the Data Connection Wizard dialog, give your connection a friendly name that you can remember. This is how you’ll point to this connection in VBA. In this case, I’m calling this connection MYSERVER.


Step 5: Excel will pop up the Import Data dialog box – Click Properties here (NOT THE OK BUTTON).


Step 6: Click on the Definition tab. Here, you’ll want to change Command Type to SQL, and then enter your Stored Procedure name in the Command Text input. As you can see, the SQL statement is simply a call to the Stored Procedure along with the Parameter name (in this case, the procedure is expecting one parameter that accepts a market name).


Step 7: Excel warns you about something unimportant – Click Yes (which in this case, means go away).


Step 8: Excel will activate the Import Data dialog box again. This time click OK to fire the Stored Procedure and return the results.


Step 9: Note the results you get from your original hard-coded command you entered in Step 6. At this point, you know your connection is working.


Step 10: Create a mechanism to dynamically select and enter your parameters.

In my case, I need to pass a market name to my Stored Procedure. So I added a simple dropdown where my selection goes to Cell B2. The mechanism you choose to select your parameters is not the important thing here. The important thing is to note where the final selection will be housed. In this example, my market selection will end up in Cell B2.


Step 11: The final step is to copy and paste this Macro into a standard module. Notice that all we are dong here is changing the definition fo the CommandText property of the Connection. This basically means that as long as you can point to the correct connection (MYSERVER in this case), you can rebuild the CommandText on the fly. Here, I’m rebuilding the CommandText to point to my chosen cell range (cell B2 – where my market selection is housed).

  1. Sub RefreshQuery()
  3.     With ActiveWorkbook.Connections("MYSERVER").OLEDBConnection
  4.         .CommandText = "EXECUTE dbo.Tng_Market_Feed '" & Range("B2").Value & "'"
  5.     End With
  6.     ActiveWorkbook.Connections("MYSERVER").Refresh
  8. End Sub


At this point, it’s just a question of firing the Macro each time your parameter selection changes.

Now you’re cooking with gas!


Next Step is to check out:
Running Advanced Stored Procedures from Excel

73 thoughts on “Running a SQL Stored Procedure from Excel with Dynamic Parameters

  1. Chris


    Further to my query on 14 May 2014, I have since re-written my stored procedure to output the results to separately stored tables within the same database. There are around 15 of these output tables. I then have a data connection in Excel for each of these output tables.

    I have something odd happening that I would appreciate any help you can provide. Apologies if the below is a bit long-winded, but I’ve tried to make it as clear as possible:

    My stored procedure takes four variables – @Service (string), @ReportStartDate (datetime), @ReportEndDate (datetime) and @Internal (string). When I refresh the stored procedure connection via VBA (with the variable values derived from specific cells on a worksheet), it runs fine for all values assigned to the @Service variable apart from one (all the other variables remain the same regardless of which value is assigned to @Service), where it seems to update all output tables apart from one. Yet when I run the stored procedure manually from within SQL Server using the same variable values, all output tables are populated correctly. I just cannot see why this output table is not being populated for one set of variables when it is for all other sets of variables?

    Any ideas as to why this is happening? I can’t post any code as it contains confidential company information.

  2. Perry

    First off… thank you! This is helping me quite a bit.

    One problem (you know that was coming)… It works wonderful in Excel 2013. In Excel 2010 and 2007, we get an Excel Run Time Error 80010108 on the refresh. We must then close ALL open Excel worksheets to do ANY refreshes.

    Very odd… but I guess this is why they call it the bleeding edge. 🙂

  3. Perry

    Well, I found the solution. There is a compatibility issue with 2013 and 2010.

    In Excel 2013, you now have a new checkbox option in the data connection. The new option is whether to refresh a specific connection if a “refresh all” is used. I did NOT want to do that, so I clicked it off in 2013. Bad choice. This is what stopped the refresh in 2010 from working. Once I turned that on, the refresh worked.


  4. Paul Bratch

    superb set of screenshots with just the right amount of relevant comment – excellent (laughed at step 7)

  5. Shabbir

    Did this worked great. Now I added a new column to Stored Procedure, for some reason the new column shows up as the last column in Excel. This new Column is not the last column in Stored procedure

  6. malcolm

    Hi ,
    I have a stored procedure that uses date from and date to . How could I adapt macro to cater for both dates.

    Format is;

    stored procedure 20150301,20150304



  7. david

    I read the problem of how to use several parameters.
    I had the same problem. To solve this you need to know what the code is doing:
    .CommandText = “EXECUTE dbo.Tng_Market_Feed ‘” & Range(“B2”).Value & “‘”

    this is building the string
    EXECUTE dbo.Tng_Market_Feed ‘Baltimore’

    for several parameters you need the string modified:
    EXECUTE dbo.Tng_Market_Feed ‘Baltimore’ , ‘Dallas’

    leading to following code:
    .CommandText = “EXECUTE dbo.Tng_Market_Feed ‘” & Range(“B2”).Value & “‘ , ‘” & Range(“B3”).Balue & “‘”

    best regards

  8. Rita

    What would the command text look like if my parameter is on a different sheet? I want one sheet to hold all my parms…separate from the data.

  9. Vicki

    Hi Rita,

    You asked: What would the command text look like if your parameter were on a different sheet. You’ve likely figured this out by now (almost a year later!), but to add to this extremely helpful site, I thought I’d post an answer.

    Change this: .CommandText = “EXECUTE dbo.Tng_Market_Feed ‘” & Range(“B2”).Value & “‘”

    To this: .CommandText = “EXECUTE dbo.Tng_Market_Feed ‘” & Sheets(“Sheet1”).Range(“B2”).Value & “‘”

    Where “Sheet1” is your parameters sheet.

    Thanks to everyone for these posts about Excel & Stored Procedures – especially the author!

  10. Pingback: Running a SQL Stored Procedure from Excel with Dynamic Parameters | Sai

  11. Vic

    Thanks for clear explanation,
    I got an error that the Stored Procedure could not be found, any clue what could cause the problem? all the tables connected and follow steps.

  12. Mitch

    I had to remove the With and End With for it to work. Otherwise it kept using the default command text and not the updated query via VBA

  13. Jim Boyer

    Hello Everyone this was very helpful post. However i did find that if you have a stored procedure that uses temp tables you have to use the “set nocount on” in your sp or it will cause excel to give you a “the query did not run or the database table…” error because it does not know how to handle the row count returns. You can also run a whole script in excels sql box but you will run into the same problem unless you put set nocount on at the top of that. Once again thank you very much for this got me on the correct path to solve my issue.

  14. Redge Shepherd

    Thank you for sharing! This is exactly what I was looking for. We recently upgraded our database to SQL Server and this solution fits perfectly with my new spreadsheet.

  15. Lee

    Is there a way to send multiple parameter values to the stored Proc. In my example I am pulling data based on an invoice number. In sQL Studio I can call the proc and put multiple parameter values in separated by commas and it will pull the data related to as many invoice numbers as I enter. but in excel, it only lets me add 1 invoice number. if I add multiple numbers separated by commas, it only returns data relating to the first one I enter.

  16. Francesco Tulini

    Excelent! Thank you very much.

    With your ideas I can join several tables and actualize the across tables including the sub-procedure in a call in the command line of a button

  17. Kent Baker

    “I had to remove the With and End With for it to work. Otherwise it kept using the default command text and not the updated query via VBA”

    Thanks Mitch. Wish I had read that more carefully 2 days ago.

  18. Sal Seech

    Fantastic bit of code. I used a view instead of an SP and dynamically built my WHERE clause in the RefreshQuery() sub. Works great in Exec 2016. Thank you, Thank you !

  19. Dawn T

    Oh, thank you, thank you, thank you! Lifesaving post. Consider updating it with screenshots from Excel 2016, since the data connections UI differs somewhat, which had me flummoxed. Fortunately, I had Excel 2013, for which your instructions worked beautifully!

  20. Muhammad Haseeb Riaz

    Please help me in it,

    When ever I create a query for date the alignment of my Excel sheet changed, date column set to last, for this I create new excel file which is lengthy process.

    Any other way to set date column as per my SQL query. Please help.

Leave a Reply

Your email address will not be published. Required fields are marked *