Running a SQL Stored Procedure from Excel with Dynamic Parameters

December 12th, 2011 by datapig Leave a reply »

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()
  2.  
  3.     With ActiveWorkbook.Connections("MYSERVER").OLEDBConnection
  4.         .CommandText = "EXECUTE dbo.Tng_Market_Feed '" & Range("B2").Value & "'"
  5.     End With
  6.     ActiveWorkbook.Connections("MYSERVER").Refresh
  7.    
  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

Advertisement

52 comments

  1. Ziggy says:

    One more trick to ask for as a result of this: the parameter-passthrough is great — but how do I also add an “all” to the parameter set? (or, for that matter, a subset?)

    ie in your case rather than simply choosing one of the five, I want some, or all of them reported on.

    Thanks – your blog is always an interesting source of Excel fun! (Yes – fun, that’s what we number freaks have with a tool like this.)

  2. datapig says:

    Ziggy: To be able to get “ALL”, you would have to alter the Stored Procedure itself to handle the “All” option.

    if @Market – ‘All’ then
    –some statement that returns all records
    else
    –some statement that uses the @Market paramter.

    Once you have altered your procedure, you can simply pass “All” as the Market.

  3. Can’t you change the SQL to:

    EXECUTE dbo.Tng_Market_Feed ‘["Enter Market"]‘

    and have Excel handle the parameter so you can link it directly to a cell?

  4. mark says:

    What a a great procedure Mike thank you!

  5. datapig says:

    Jan Karel: I don’t think that would work when specifying a connection to SQL.

    However, you can do this:

    1. In step 1, choose ‘From Microsoft Query’ instead of ‘From SQL Server’.

    2. Go through Step 2, 3, and 4 as described in this post.

    3. In Step 5, you could use something like this

    Exec SP_GetEmployeeManagers ?

    The question mark (?) will be understood as a parameter.

    From here, the query will ask for the parameter value.
    At this point, you can set your parameter to link to a cell.

  6. Giorgio Rovelli says:

    What does Exec SP_GetEmployeeManagers do and shouldn’t it be used in step 6?

  7. Eric says:

    Mike,

    I’ve been applying this concept, but have run into a problem with displaying dates. The data source provides “date” information in milliseconds counting from 1/1/1970. Where would you apply the conversion? Is there a way to put it into the SQL? Or would you do the conversion when you do the analysis?

    Thanks,

    Eric

  8. Bob says:

    This example was very helpful. Do you have an example that insert/update/deletes data on SQL Server using a stored procedure?

  9. Nathan says:

    Great stuff! Thanks! I’m trying to do the same myself, my try returns the results from my stored procedure as expected BUT it also wipes out all the cells above it including my button. Did you experience this wipeout? Any way to avoid it?

  10. John Jagoe says:

    I put your code in the SelectionChange event, so the result only update if I click on another cell, not whe the selection changes. Where do I put your code so that when the criteria changes, the results change?

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With ActiveWorkbook.Connections(“MYSERVER”).OLEDBConnection
    .CommandText = “EXECUTE dbo.SP_Test_Excel ‘” & Range(“E2″).Value & “‘”
    End With
    ActiveWorkbook.Connections(“MYSERVER”).Refresh
    End Sub

  11. Mohammed says:

    Thanks for the detailed step by step guide. I tried everything mentioned here but it did not work for me. I am using Excel 2010 if that makes any difference. I get an error message saying “the query did not run, or the database table could not be opened.”

    Any suggestions? When I do it with a random table it works fine, but not with the procedure. I tried different procedures but no luck.

    Thanks

  12. jim says:

    what is the syntax if you have more than one parameter you wish to narrow down on or use a parameter that is not the first column of data eg in your data Product category?

    thanks

  13. sarma says:

    How to do it, If i have to input the parameter in Sheet1 and want to have a “Submit” button in Sheet1.
    Once if user clicks the “Submit” button i want to show the results in Sheet2.
    Can any one help me out?

  14. MADHU says:

    Thanks.

  15. BigQuestion says:

    PLEASE, explain how to pass 2 or more filter values to one parameter field ? or a named range with list of 2 or more values to this parameter field.
    Thanks.

  16. datapig says:

    BigQuestion; Parameters don’t usually work that way. That is to say, a SQL StoredProcdedure will not typically accept more than one value in a singel parameter.

    For example: a parameter called @Market will not typically accept ‘Tulsa, Denver, Phoenix’.

    I suppose it could work if you used an IN clause in the Stored Procedure.

    How are you usng the Parameter in the SQL Stored Procedure?

  17. Venkata says:

    Big Thank you! I did succeed using your approach using oracle oledb.
    Step1. Established connection using a select query with where clause one where condition so that I get small result.
    Step2. Replaced with ? in where clause in the query in properties window.
    Step3. Placed in a cell drop down list with a named range and selected parameters to look into this cell with automatic refresh… got quick results.
    Step4. As an alternative placed combobox drop down with same named range and rest same as above step3 for parameters details to look into the linked cell… got quick results.
    Step5. Played the above with pivot table and pivot tables connections used the same above oledb connection.. pivot’s connection properties.. followed same for parameters lookup… got quick results.. but had to use a button to click to invoke a 4/5 lines macro to refresh the pivot table. sometimes working sometimes not working.
    Step6. ALl the above are good with one parameter value selection. IN (‘Tulsa’, ‘Denver’, ‘Phoenix’) would be ideal for my need… I need help on either list box or combo box multi select based linked cell or another dependent cell gets my selections appear as (‘Tulsa’, ‘Denver’, ‘Phoenix’).
    Step7. I can say “VOILA”.. with multi parameters for simple select query without even using stored procedure. PLEASE HELP.

  18. Thomas says:

    How do you apply multiple parameters with the MS Query version of doing this? I’ve tried just adding the requisite number of ?’s with a space in between and it askes for the cell in put for the first, but not the rest.

  19. Venkata says:

    ok.. looks like we just have to use some cheap trick.. use IN parameter in clause in sql query.. and in excel capture the input parameter values in a chosen cell …if string based wrap them up IN ( ‘single’ , ‘quotes’ ) . That should take care of it.. Thanks to all..

  20. Kent says:

    Thanks for this! – I have been trying to crack this nut for days. Utilizing a stored procedure on Sql Server I had to pass a DATE to the stored procedure and formatted the date taken from B3 this way into the CommandText:

    .CommandText = “EXECUTE dbo.GetDBData ‘” & Format(Range(“B3″).Value, “yyyy-mm-dd”) & “‘”

  21. John says:

    I have the same problem as Mohammed…
    The problem seems to be limitations in the code that you can use in your stored procedure…
    I have a stored procedure that has one complex select with 7 openqueries and many sub queries with no Parameters for the SP. This one works fine…

    I try the same steps with a more complex SP that has variables and 2 cursors… for that stored procedure, I get the same error as Mohammed which is:

    The Query did not run or the database table could not be openned….

    Does anyone know what the limitations within the SP can be when using within Excel?

  22. John says:

    Got it!!! Just needed to read further… LOL

    The answer is in the other thread Running “Advanced SQL Stored Procedures from Excel » Bacon Bits” in the Trackbacks/Pingbacks section…

    all you need to do is to put “Set NoCount ON” at the beginning of the SP….

    Great Post!!!
    Thank you

  23. SS says:

    How can I get the RefreshAll() sub to fire off when I hit “RefreshAll” on the Data Menu? It works fine when I run the macro itself- but the data doesn’t refresh when I hit Refresh All..maybe coz the hard-coded parameter in the connection string overrides it?

  24. dianadi says:

    This example is very helpful.thanks, but where input the Sub RefreshQuery() function ? PLEASE help

  25. Monstah says:

    Great help. I have spent some days with a question I haven’t found the answer to, yet.

    I have an external ODBC source linked to excel, but my queries don’t need just one-cell parameters, but tables of parameters. I know I could, in the simplest case, have VBA concatenate a range with commas, and use SELECT WHERE IN (…), with the VBA-generated comma-separated list inside the IN clause.

    However, it’s not always (usually) the simplest case, and my parameter table sometimes has hundreds of records, which makes the IN clause very, very inefficient. What I’d like to do is something along the lines of

    SELECT odbc.*
    FROM odbc
    JOIN [named range on this same workbook] ON etc…

    Is that possible? Thanks in advance.

  26. Monstah says:

    Hm, appearently, I am not the first one to ask a variation of this same question.

  27. Charles Cruz says:

    AWESOME!! I have been looking for a solution like this and this works great. Thank You!!

  28. jd says:

    Can you amend the VB so that it runs without the parameters . Many thanks for this

  29. Watrachos says:

    Hi datapig,

    thanks for the great help. You showed us how to select a parameter from the combo box. What if the content of the combo box is not known at design time? In your example seems to be a fixed list.
    In reality I want to show the list of all cities found in my database table.
    How would you go about that?

    Also, have you got an idea in case I need a “Lookup” or “Drilldown”? This is: I want to click on one of the retireved data rows and get further information about that record (or related records) from the database and show then in a table or grid.

    Any further help is appreciated
    Thanks
    Watrachos

  30. edy says:

    With static parameters it works fine in Excel 2010. But… i can’t get it work with dynamic parameters. When I use the macro, it refresh connections using the hard-coded parameters, not the dynamic string. Any idea?

    Thanks

  31. bin says:

    Hi,

    May I know if there is a way to do all of this in vba code? For what I am trying to achieve, it is difficult to do everything in the wizard, because I am storing the results into a different sheet. I have multiple parameters to pass and I need to process the parameters’ value from the user input and pass it to the stored proc.. Thanks a million!

    Bin

  32. christine says:

    Attractive section of content. I just stumbled upon your weblog and in accession capital
    to assert that I get actually enjoyed account your blog posts.
    Any way I’ll be subscribing to your feeds and even I achievement you access consistently rapidly.

  33. brian says:

    Excellent blog you have here but I was wondering if you knew
    of any community forums that cover the same topics discussed in this article?
    I’d really like to be a part of group where I can get feed-back from other experienced people that share the same interest. If you have any suggestions, please let me know. Kudos!

  34. I really like your blog.. very nice colors & theme.
    Did you make this website yourself or did you
    hire someone to do it for you? Plz answer back as I’m looking to create my own blog and would like to find out where u got this from. thank you

  35. This excellent website truly has all the information and facts
    I wanted about this subject and didn’t know who to ask.

  36. kevin says:

    Hello! I know this is kinda off topic but I was wondering if you knew
    where I could find a captcha plugin for my comment form?
    I’m using the same blog platform as yours and I’m
    having difficulty finding one? Thanks a lot!

  37. anna says:

    May I simply just say what a comfort to uncover a person that genuinely understands
    what they’re discussing over the internet. You definitely realize how to bring an issue to light and make it important. More people have to read this and understand this side of your story. I was surprised you’re not more popular since you certainly have the gift.

  38. Malcolm says:

    Hi Thanks for this great insight. Would it perhaps be possible to advise on running a stored procedure that utilises a from and to date?

    eg extract ’2013-01-01′ ’2013-05-01′

  39. Michael Weber says:

    This works great with one exception.

    When I execute the .refresh command to refresh the data in excel there is a slight delay getting the results. How do I have the VB program ‘wait’ for the results before it tries to use them?

  40. Glen says:

    Great But I am struggling to send 3 parameters to my SP, in the sql comand text i have “Exec getcolours ‘Formica’ ‘Storm’ ‘Velour’ ” and in the stored procedure I have
    PROCEDURE getcolours
    @brand varchar(50), @colour varchar(50), @finish varchar(50)
    AS
    BEGIN

    SET NOCOUNT ON;
    Declare @count int

    set @count = (SELECT COUNT(tbloffcut.offcutid) AS Expr1
    FROM tbloffcut INNER JOIN
    tblcolour ON tbloffcut.colourid = tblcolour.colourid INNER JOIN
    tblfinish ON tblcolour.finishid = tblfinish.finishid INNER JOIN
    tblbrand ON tblcolour.brandid = tblbrand.brandid
    WHERE (tblcolour.colour = @colour) AND (tblbrand.brand = @brand) AND (tblfinish.finish = @finish))
    SET NOCOUNT OFF;
    return @count
    END

    The values I wish to pass are from F2, J2 and O2 in my spreadsheet but at the moment just trying to send more than one value stuffsa up in the data connection,

  41. Jerry says:

    I have seen the question asked multiple times in this thread, but never see the answer.
    How do you force the refresh VBA code to fire when the user clicks the Refresh All button on the Data tab ?

  42. Roshan says:

    HI all
    CommandText = Array(“[pohistory]‘” & Range(“A2″).Value & “‘” & Range(“B2″).Value & “‘”)

    this with two param .. didn’t work any idea ?

  43. Ned says:

    This is great. Thank you very much

    Ned

  44. Alexis says:

    Hi!

    Great post, thank you very much!
    Do you know how I could change the database instead of the command text in step 11 with a VBA comand line (same query but on a different database…)?

    Thank you!

    Alexs

  45. Brendon says:

    Hi,

    Is it possible to select a single record from a SQL connection using a named cell in excel i.e. Client_ID.

    Don’t mean to highjack the blog but this one almost does what i need and cannot find the information anywhere else.

  46. Chris says:

    Hi,

    I’m fairly new to SQL Server so please bear with me. This will really help with bring back data from a stored procedure that I have.

    However, my stored procedure has more than one select statement. Following the above steps, it appears at the moment to only bring back the results from the first select statement in the stored procedure. How can I get it to bring back the results from all select statements?

  47. Eileen says:

    This site has been very helpful, although I feel like something is missing. I got my VBA to run, but when it runs, it spits out the answer to the data connection command text, regardless of what I have in the cells I reference in the code. What am I doing wrong?

    Public Sub Button1_Click()

    With ActiveWorkbook.Connections(“Reconciliation”).OLEDBConnection.CommandText = “EXECUTE AAMS6_Reports.dbo.sp_Reports_Contract_Order_Reconciliation ‘” & Range(“A2″).Value & “‘,’” & Range(“B2″).Value & “‘”

    End With
    ‘Refresh Query
    ActiveWorkbook.Connections(“Reconciliation”).Refresh

    End Sub

  48. datapig says:

    Chris: If the Select statments bring back the same number of columns, you can use a UNION ALL statement?

    Otherwise, you’ll have to use separate procedures.

  49. datapig says:

    Eileen: Each time you change the data in your cells, you need to fire the macro (press the button).

  50. Muthu says:

    I would like to know how to get the drop down filled up with our values from Database and based on the input we select Stored procedure should give us the result set. If anyone can help me please do it. (The I/P that I select to execute SP dynamically will be based on some tables from Database). Post image like above. I successfully get the result set in Excel from executing SP with STATIC INPUT. Give me solution for Dynamic I/P for SP thro EXCEL drop down.

Leave a Reply