Running Advanced SQL Stored Procedures from Excel

A while back, I posted two articles that showed you how to run Stored Procedures from Excel.

In “Running a SQL Stored Procedure from Excel“, I showed you how to fire a simple SQL Stored Procedure that returns a simple dataset. In “Running a SQL Stored Procedure from Excel with Dynamic Parameters“, I showed you how to pass parameters to a SQL Stored Procedure so you can filter the returned dataset.

.

In both of those examples, the common denominator is that the Stored Procedure can only contain a select statement. For example, ‘Select * from Market Sales‘.

.

But in some cases, your Stored Procedures may need to contain advanced functionality such as; creating temp tables, deleting data, updating data, etc. In these cases, you can’t simply fire the Stored Procedure from Excel. You’ll need to adjust the Procedure so that the results will return to Excel.

.

The Cause of the Problem

When you create a new Stored Procedure in SQL Server, you will automatically get a line that reads SET NOCOUNT ON. This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is intended to improve performance by suppressing unnecessary messages to the client.

.

However, this will also suppress the returning of data to Excel, because SET NOCOUNT ON tells SQL Server that there is no need for this information to be passed back to the client.

.

The Fix

In this example, procedure, I’m creating two Temp tables in the procedure then creating a select statement which returns data from the two created tables. In order to be able to return the data to Excel with this Stored Procedure, I will need to add a line at the end of the Procedure that reads SET NOCOUNT OFF.

.

.

By wrapping your Stored Procedures with SET NOCOUNT ON … SET NOCOUNT OFF, you can fire all kinds of Stored Procedures from Excel:

  • Stored Procedures that create and use Temp Tables
  • Stored Procedures that contain Delete and Update Statements
  • Stored Procedures that contain Truncate and Insert Statements

.

In short, you can use this trick in conjunction with the other tricks you learned (in the other articles mentioned above) to trigger virtually any SQL Stored Procedure directly from Excel!

29 thoughts on “Running Advanced SQL Stored Procedures from Excel

  1. Christophe

    You saved my life.

    After hours of search / debugging / …, I was unable to understand why Excel refuses to refresh my query and now, thanks to your post, I understand why.

    THANKS !

  2. Drew

    For those of you coming here in search of how to solve the “ms query shows data from sql server but then returns no rows to excel” issue, this is how. Wrap your query with set nocount on/off and ms query returns data. I started having the problem when we upgraded from 2007 to 2010.

  3. Jason

    This is great stuff. I used the code you had from the previous post about Stored Procedures executing from Excel with Parameters. I have one situation where I set the parameter in the stored procedure to Null and if it is null it sets the parameter to the value in the transaction, thereby getting all values. For example if I see a value of 8 in the month parameter you only get the month of 8 but if there is no value, you will get all months. My problem is I can’t figure out how to send the null in the connection string. Any advice on that?
    Thanks

  4. Carl

    What a relief! In 2005-05 I ran all kinds of queries from Excel without a hitch. Today I was tasked to run some similar stuff via Excel against a 2008 SQL server. . The process was jamming everytime. This was the missing link! THANKS!!

  5. Manfred

    Hi,

    i have some rather complex stored procedures, which dynamically create table variables and so.

    When writing the T-Sql procs I inserted a print @sql, which i forgot to remove. SSMS doesn’t mind and returns the result set as well as the print message. Interestingly, Excel only returns the dynamic sql code as message, but no result set. I removed that but the problem persisted. Scratching my head I did a search and found this article – probably saving me from suicide. I enclosed the DECLARE @xy TABLE and Insert statements in SET NOCOUNT ON/OFF and everything worked again.

    Thank You sooooo much for this very helpful article.

  6. derek

    For those wanting to pass in multiple parameters, I was able to get it to work using VBA. Just separate the parameters with a comma. Put them in the same order as you declare them in your SQL.

  7. Steve

    I am calling a parameterized stored procedure to return a dataset (in SQL server) from within Excel powerpivot. Everything works fine but it seems to be taking too long to return the data. The sp runs in less than 5 seconds when executed in SQL server but takes 1-5 minutes when executed from excel. Any ideas?

  8. Unni

    I had to leave this comment to thank you. This post has helped me solve my problem with a stored procedure. Thank you so much. You dont know but you are a life saver 🙂

  9. Pingback: Running Advanced SQL Stored Procedures from Excel | Sai

  10. JORGE LOPEZ SAAVEDRA

    Hi,

    Is there a way to create a user defined function (UDF) that takes parameters from a stored procedure (SP)?
    Did I make myself clear?

    For example, having a stored procedure to return 1 number based on 2 parameters. However, I want to call the stored procedure from a UDF that takes its inputs from 2 cells in excel. The magic of this: is that you can copy-paste it down based on parameters that exist in 2 columns.

    I have seen this at work. There are UDF’s that connect to a database in SQL. But I dont know how they do it. Besides, in order to work, Im forced to install an add – in that they have created.

  11. Dilip

    Thank you so much…i was getting the error 91 and the connection closed. This technique resolved it. God bless u!

Leave a Reply

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