The mysterious “the statement did not return a result set” SQL Server Exception

Last week I worked on a SQL server stored procedure that looked something like this

create procedure sp_Awesome_Proc
(
  @numbers XML,
  @customerId int
)
AS
BEGIN
declare @output table(......)
insert into @output(....) select .... from ...
... some more updates and joins ...
select * from @output
END

This stored procedure was called from Java using Spring’s JdbcTemplate

query("exec sp_Awesome_Proc @numbers=?, @customerId=?", new Object[] {a, b}, new RowMapper {....}); 

When I called the stored procedure via the above Java code, I kept getting the exception SQLServerException: The statement did not return a result set. However, if I used the same parameters and called the stored procedure within SQL Server Studio, it returned a table.

It turned out that if the stored procedure performed any inserts or updates prior to the final select, this baffling statement did not return a result set exception will be thrown. In my case, a series of queries were executed to populate a table variable which is returned at the end of the stored procedure.

A simple solution to this problem is to add SET NOCOUNT ON at the start of the stored procedure.

create procedure  (
  @numbers XML,
  @customerId int
)
AS
BEGIN
SET NOCOUNT ON 
...
END

In fact, all the stored procedure within our code base had this statement. I have been copy and pasting this into all the previous stored procedures I created, without knowing its significance. Only now I learned the why behind it.