I have a stored procedure sp_1 that calls another stored procedure sp_1_1.
I know how to suppress the results from sp_1_1 using this trick.
The real problem is that sp_1_1 itself also call another stored procedure sp_1_1_1 which ALSO returns it's results to sp_1_1!!
I may not change either sp_1_1 or sp_1_1_1, and can only change sp_1.
The results returned to sp_1 are 2 recordsets, with the first from sp_1_1 and the second from sp_1_1_1.
SUMMARY:
sp_1 (Needs to suppress two recordsets returned from below)
+---- sp_1_1 (returns its own results, then results from below)
+----------sp_1_1_1 (returns results)
Firstly, an aside, SQL Server "Denali" gives you new options for handling resultsets.
In this case, because you're nesting you obviously can't use the insert/exec trick.
One kludgy workaround, if you don't want to refactor too much, is to add a parameter to the proc with a default, something like @SuppressResults bit = false.
Then, in the routines that need to be nested, call it explicitly with @SuppressResult=True, and then alter the code in the routines to not select results if @SuppressResults=True.
The important thing is to provide a default and put the parameter at the end. That will prevent existing calls to the routine from elsewhere in the code base needing to be altered.