Thursday, January 5, 2012

Get data from a SQL Server Stored Procedure into Excel automatically

Intro:

Do you want to get data from a stored procedure in SQL Server and have it drop right into MS Excel automatically? I recently needed just that, so follow this simple tutorial to get the job done right away...

How to run a SQL Stored Procedure from Excel without using VBA, and even include dynamic parameters that can be stored in cells in Microsoft Excel!!!



To pull data from a SQL Server Stored Procedure in Excel using parameters, do the following:


Excel 2007 Instructions:



1.  Select the Data tab on Excel's Ribbon, then within the Get Exernal Data group choose the "From other Sources" drop-down.  Then Choose "From Microsoft Query"

2.  Within "Choose Data Source" pop-up box, select your SQL Server, then hit OK.

3.  Close the "Add Tables" popup if necessary.

4.  Click on the "SQL" button, or choose View > SQL to open the SQL pop-up editor.

5.  Enter the following syntax:  {CALL myDatabaseName.dbo.myStoredProc (?, ?, ?)}

    For example:  {CALL northwind.dbo.spGetMaxCost (?, ?, ?)}

    Be sure to include the squiggly braces around the call statement. Each Question Mark (?) indicates a parameter. If your stored procedure calls for more or less parameters, add or subtract question marks as needed.

6.  Hit the OK button. A question box should pop-up saying "SQL Query can't be represented graphically, continue anyway?", just hit the OK button.

7.  You will now be asked for sample parameters for each question mark you included above. Enter valid parameter values for the data you are querying.

8.  Once you have entered the last parameter, you should get some results back in Microsoft Query. If they look good, close Microsoft Query.

9.  You should now be looking at an "Import Data" pop-up. Click the Properties button, which will bring up the "Connection Properties" pop-up.

10.  Select the Definition tab, then select the Parameters button. You should now see a "Parameters" pop-up, where you can connect the parameter to a specific cell.

11.  Select Get the value from the following cell, and then connect to an appropriate cell in Excel that will hold your parameter, by clicking the little box with the arrow.

12.  If you want the data to refresh every time you change the cell containing the parameter, check the box stating "Refresh automatically when cell value changes"

13.  Continue as above for the other parameters. When finished, click OK, to return to the Connection Properties pop-up. Click OK to return to the Import Data pop-up, and click OK again.

14.  You should now have some data straight from your stored procedure.



Hope this helps someone out there!










Here are some common error messages that you may experience when trying to do this...

     No value given for one or more required parameters.

     Parameters are not allowed in queries that can't be displayed graphically.

     Could not find stored procedure ...    







Tags and Common Search Phrases:



connect excel to stored procedure

Excel Get external data from stored procedure
Microsoft query to SQL Server stored procedure from excel
get data into excel from stored procedure
add parameters to excel for stored procedure

22 comments:

  1. great, thank you. Can you pass mutliple stored procedures or use a select statement after calling a stored procedure. for example,
    {CALL northwind.dbo.spGetMaxCost (?, ?, ?)}
    select * from mytable

    ReplyDelete
  2. You have absolutely no idea how long I was looking for a how to on this...
    Thanks a mil.
    Just a small recommendation/change on step 8: don't simply close the query, but select the option to Return Data (the icon containing the open door with the arrow pointing toward it). Simply closing the query did not provide me with the Import data popup in point 9.

    ReplyDelete
  3. Does this still work correctly in Excel 2010?

    ReplyDelete
  4. Yes, I agree - your post is brilliant, I have spent ages searching through other posts about writing VBA macros and all sorts of complicated stuff when I knew the answer had to be out there somewhere
    Thankyou!

    ReplyDelete
  5. Thanks a lot Joshua, you saved me a ton of time as well. I can confirm it works properly on Office 2010.

    ReplyDelete
  6. Thank you - this saved me time today when I needed to set it up. It's slightly different, but close enough in Excel 2010. :-)

    ReplyDelete
  7. HI Joshua,

    This is a great post. The solution is perfectly working. I have one small problem after executing the procedure I need to use a select statement to pull the data from the table. The reason is the my stored procedure insert records to a temporary table. Since temporary table data can be pulled only on the same session I am not able to achieve pulling data to excel. Is there a way to give like
    1. {call procedure(?,?)}
    2. select * from table used in procedure.
    If i split in two different queries data wont fetch.

    Kindly guide me how can I achive that.

    Thanks & Regards,
    Ashok

    ReplyDelete
    Replies
    1. Ashok,
      Try to rewrite the query without using a temp table. Attempt using a common table expression (CTE) if possible instead of a temp table or possibly a sub select.
      Joe

      Delete
  8. Ashok,
    All you need to do is place the Select * into the stored procedure you are calling. So at the very end of your SP, you will have something that looks like this:

    select *
    from #table
    end

    ReplyDelete
  9. Hello,

    I have a stored procedure that has 4 parameters. But one of the parameters can have multiple values.
    How do you pass multiple values to a single parameter?
    I have 5 cells that have the values in them for the multi value parameter. These values can be changed by the user. I need to pass those values to the stored procedure (in one of the parameters). How can I do this please?


    Harish

    ReplyDelete
    Replies
    1. Harish,
      It depends on how your procedure is using them. For example, do you combine them into a comma separated list used in an IN clause or do you treat them as a table in a JOIN or what exactly. Once we understand the procedure and how you use it when Excel is not involved we might be able to help.

      Delete
  10. I have the variable linked to a specified cell. Is there a way to pass a NULL value to the variable?

    This is my situation:
    SELECT * FROM MyTable WHERE column = IsNull(@MyVariable , column)

    ReplyDelete
  11. I am getting an error The database component of the object must be the name of the current database

    ReplyDelete
  12. Thank you for this step-by-step tutorial. Immensely helpful.
    This may seem really dumb but for the life of me I can't get Excel to prompt me for the parameters. Yes I chose the prompt option in step 11. I've tried hitting refresh in like 3 different places, edit query, connection properties etc. How do I get Excel to prompt me for the parameters?

    ReplyDelete
    Replies
    1. Never mind. It's because I checked on 'use this value/reference for future refreshes'.

      Delete
  13. What should Id do to the ? section If I have no parameters at all?

    Below are the necessary details -

    Database Name – ZZ_Common
    SP Name – Update_Vol

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. Dear Joshua

    I get the Error Could not find the store procedure............. pl help me out what shud i do next?????


    Atul

    ReplyDelete
  16. Very nice solution. Assuming I am restricted in modifying the stored procedure called I want to take the output from above and join it with other information that I can get to. I want to treat the {call ....} as a results set and can't seem to manage it. Any more magic tricks you have would be greatly appreciated. --Bill

    ReplyDelete
  17. Hi Joshua,

    How can this be distributed to business users? I have created report using above bullets. When I send this report for testing connection through error. How this report can be distributed? I appreciate your help in advance.
    Sapna

    ReplyDelete