How to ignore and not return result sets

[Richard Andrew x64]

I have discovered that in T-SQL for Sql Server 2022, if I run a stored procedure that happens to call another stored procedure that returns a result set, then the server returns both result sets to my application. How can I, within T-SQL, specify that the result set of an embedded stored procedure call is not part of the return to the client? Is there a way to specifically choose?

The difficult we do right away... ...the impossible takes slightly longer.

[Richard Deeming]

For a single resultset, you may be able to use INSERT .. EXEC to dump the resultset into a temporary table / table variable.

DECLARE @tmp TABLE ( ... );
INSERT INTO @tmp EXEC yourStoredProcedure ...;

But if you control the other stored procedure, a cleaner option would be to add another parameter to suppress the output.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Richard Andrew x64]

Thanks, Richard. I'm frankly surprised that there's no documented way to return only specific result sets. I'll probably end up using the additional parameter to suppress output. Cheers.

The difficult we do right away... ...the impossible takes slightly longer.