This reminds me of a situation I encountered where a stored procedure was querying an Analysis Services cube, storing rows in a temp table and then returning results to Reporting Services. Sometimes (on certain dates / regions) the cube would not return any results. Worse, when it happens it doesn't even return the column headers, so the insert into the temp table doesn't then insert a null record or nothing at all, it just has an error.

In other cases, not all measures are returned, and rather than the empty measure column returned as NULL, they are just left out of the result set completely so you have a column mismatch e.g. the insert is expecting 10 columns but sometimes there's only 9.

In some database development I've seen the use of Return Values in stored procs in communicate success or failure back to the front end code. Better is to use TRY/CATCH and RAISERROR which is the fix I used on the previous Analysis Services example. Specifically I didn't raise errors because often that has its own problems, I just used try to test the returned rows and catch to insert either null row or limited columns as required.

I guess some sort of message always needs to be passed back to the calling code, and whether you are relying on the database provider/connection handler to do that implicitly or you handle it yourself, not returning anything at all is going to result in a time-out at best and an endless wait at worst, as opposed to an empty set which is an actual a "thing" that you can do something with.

Like your question though, that's an academic answer because mostly a non-result will in practice be handled for you and "something" will be returned.