Running a SQL Stored Procedure from Excel (No VBA)

We all know we can use MS Query to get data from a SQL server. Typically though, we pull from a Table or a View. Well in some organizations, the IT department wants all interaction with the server to be done through Stored Procedure. This adds a level of risk management and makes the DBAs feel better.

 

So today, I’ll show you how to easily make Excel run a Stored Procedure to get data.

 

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

 

 

Step 2: Enter Credentials. Your server name can be an IP address

 

 

Step 3: Choose any old table or view. Pick a small one because we’ll discard it later anyway.

 

 

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

 

Step 5: Click on the Definition tab. There, change Command Type to SQL, and then enter your Stored Procedure name in the Command Text input.

 

 

Step 6: Excel complains about something….blah…blah…blah. Click Yes – (as in yes I know what I’m doing).

 

 

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

 

 

Step 8: Marvel at your results

 

 

Notes:

  1. Excel will fire the Stored Procedure each time you “Refresh”
  2. If you have to pass a parameter, you can enter it in the command text like this:

     

  3. If you have to pass dynamic parameters you’ll have to turn to VBA. See this post.
  4. I assume you can do this with ORACLE databases too.
  5. I’ve yet to test whether this will fire a Stored Procedure that doesn’t return data. In other words, Stored Procedures that perform Insert, Update or Delete actions. I assume that if you can, there is the possibility of updating SQL from Excel through a simple connection. Pretty cool.

27 thoughts on “Running a SQL Stored Procedure from Excel (No VBA)

  1. jeff weir

    If you have to pass dynamic parameters you’ll have to turn to VBA. I’ll do a post on this later this week. Goody! Perfect timing, I have to do one of these at work.

    Dick et al answered some questions for me on this at http://www.dailydoseofexcel.com/archives/2010/04/28/dynamic-columns-in-ms-query/ and http://www.dailydoseofexcel.com/archives/2010/04/28/dynamic-columns-in-ms-query-ii/ in case this helps in any way.

    And I found a lot of info at http://www.sommarskog.se/dynamic_sql.html that I’m working my way through that looks very comprehensive. But I’m looking forward to just the facts, maam re VBA

  2. Luis

    Hello, Thanks for this blog, very helpful, I have a problem, for some reason when I enter a parameter to my sp, I get a “Referance not valid” message….Would you know why?

  3. Kevin

    This is dangerous on a lot of levels (password storing in excel to a “live” database).

    Other issue is that the database vEmployee could be enormous in terms of fields (columns) and records (rows). If that database has 35000 employees (terminated and active) and there are 125 fields. Hope you have a lot of processing time.

    As for Oracle (ie PeopleSoft) there are 500 tables that have to be joined. Good luck with that….

  4. datapig Post author

    Kevin:
    Your points are valid. I would counter with these:

    1. You can use a ‘Service’ account that only has access to run controlled Stored procedures

    2. The Stored Procedures should be tested and designed to minimize performance issues. Because the user is going through a Stored Procedure, it’s actually less dangerous than if they built an MS Query on their own.

    3. Again, because the user is going through a pre-designed Stored Procedure, they would not have to know every nuance of the database. So if there are 500 tables to join, it would essentially be transparent to the user , as he is running a single Stored Procedure.

  5. help!

    What if I need to pass two parameters? I’m separating with a comma and its only reading the first param.

  6. Helen Wang

    This is very helpful! Would you give me the link to your article to accept dynamic parameter values?

    Thank you very much!

  7. virginia

    Hey, I would like for excel to get this “parameter” by code, because of this problem:
    I have to send a company ID to the procedure so that we filter by company ID, but that ID is in a session when someone runs my program…
    how could i send that information on the session to the excel file, so that it sends it to the procedure…any ideas?

  8. Iain

    I do this and it works fine, even when I need to pass parameters. But the issues i’m having is that Excel is not arranging my columns in the same manner the Stored Procedure returns them, How can I get Excel(2007) to play nicely? Thanks.

  9. Iain

    You can all ignore my question. I restarted with a clean slate. Deleted the spreadsheet contents and re-did the Data source and all the columns are now in line with he Stored Procedure results. The issues was with using a previous spreadsheet and modifying the Data Definition, caused Excel to get confused. My Bad…

  10. y Li

    can I write a select statement after calling the stored procedure?

    eg.
    sp_getemployeeemanagers ‘North’
    select * from mytable

    Thank you

  11. Nishchinth Kumar

    I have almost 70 databases in 3 servers… can we run the same query in all databases and get the details…. And most important is each result set of database should be copy pasted in a empty row after the filled row?

  12. John

    This was great. We were able to call a function in sql and pull back the data we wanted. The code was
    DECLARE @Plant varchar(20) = ‘xxxx’
    SELECT * FROM YYYY.dbo.fnc_BusinessDateFind(@Plant, getdate())

    Very simple, but met our needs.

    Thanks!

  13. Stephen

    The SP I fire off uses AVG() or SUM(), so when a NULL is found in the set, SQL Server emits “Warning: Null value is eliminated by an aggregate or other SET operation.” Can this be suppressed in some way?

    Also, I have a RAISERROR(‘<>,0,1) WITH NOWAIT to give a simple audit trail of executions. It appears that Excel sees this first and doesn’t process the resultset. Clues as to how to get Excel to ignore such messages?

    Lastly, I followed the steps above, and saved the initial connection, but after I changed from table to SQL, there was no “Save” option, so when I open the connection to edit it, I get back the original “table” style instead of SQL. Ideas?

    Thanks for this and the Dynamic article – I have statisticians clamoring for data directly from the DB and it can only be delivered by an SP that makes sense of raw data. So this is incredibly importatnt stuff…

    Cheers!

  14. Etienne BLey

    I love your comment about the DBA’s feeling better – I think that surpasses the security aspect. Thanks for the lol – it was a belly laugh actually.

  15. Chris Hanson

    This is such a great solution, it makes both my inner developer and DBA happy!

    I was able to fix a thorny problem in about five minutes rather than spend the afternoon to rejigger a bunch on VBA in my users spreadsheet.

  16. RespondForHelp

    help!
    says:
    October 6, 2011 at 12:16 am
    What if I need to pass two parameters? I’m separating with a comma and its only reading the first param.

    Another way with mutilples parameters:

    In command text, type your text as below:

    EXEC [SeveurName].[dbo].[Name of procedure]
    @DIRECTION = N’TOUS’
    ,@CIS = N’TOUS’
    ,@PARC = N’TOUS’
    ,@GROUPE = N’TOUS’

    Where :

    @DIRECTION , @CIS… are the parameters in your procedure

    TOUS is a value of parameter
    Do not forget comma between parameters.

  17. Deep Dave

    Hi,

    I have a Stored Procedure with no Parameters.

    What should I do in such a case?

    Below are the details required. Just wanted to know how my statement will look like?

    Database Name – ZZ_Common
    SP Name – Update_Vol

  18. Martin Packer

    This works great, but one suggested addendum: I needed to add “set nocount on” to my stored procedure. I don’t know if that’s always the case, but it was for me. Otherwise I got “The query did not run, or the database table could not be opened [yada, yada]”.

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

  20. Pingback: Running a SQL Stored Procedure from Excel (No VBA) | Sai

Leave a Reply

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