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!
