CODEWITHSUNDEEP
c#sql-serversqlclient
CodeAngry
CodeAngry

Reputation: 12985

Suppress stored procedure output when called in another stored procedure?

So I have sproc1 which does some things and returns some rows. The important thing is it does some things. I also have sproc2 which does some things, calls sproc1 (which does it's own things) and returns it's own rows. The problem is when I call sproc2 I get 2 result sets. First comes from sproc1 and second comes from sproc2.

Is it possible to easily suppress the sproc1 when calling it in sproc2?

I have two ways to do this as far as I can tell:

None of these methods is easily reusable as the:

The easy way would be if SQL Server allowed me to exec a stored procedure within another stored procedure, but suppress the output of the inner executed it. Or if SqlCommand allowed an ExecuteReader(CommandBehavior.LastResult) and would navigate to the last result by itself.

Can any of the two be achieved in an easy and reusable manner?

The real solution would be to refactor inner stored procedures into write and read components. Or add a @param to inner stored procedures that prevents the final results from being selected. But I'm trying to be lazy here!

Upvotes: 4

Views: 1809

Answers (1)

CodeAngry
CodeAngry

Reputation: 12985

So (for now, unless I find a better answer or something gets improved) I ended up adding this argument with a default value so I don't have to think about it at all in the C# side:

,@_Suppress bit = 0 -- prevent output via select

and right before the select I add:

if @_Suppress is null or @_Suppress = 0
select -- output results

This method also requires you to refactor insert ... output code and output into a temporary table and eventually only select if not suppressed.

This is the easiest method to handle things but there should be internal functionality for these cases like:

begin suppress
exec some_sproc;
end suppress;

or some special syntax like sexec (as in suppressed exec) or a general use NULL table that can accept any insert columns format and just discard it.

I'll probably add this argument from now on to all my sprocs that produce results and refactor the old ones impacted by this issue.

Upvotes: 1

Related Questions