CODEWITHSUNDEEP
t-sqlstored-procedures
Brian
Brian

Reputation: 38025

How do I suppress the results from a stored procedure from within a stored procedure?

I've got a stored procedure (we'll call it A) that calls another stored procedure (we'll call this one B). B includes a SELECT that I do not want to send back to the caller of A.

Here is some really rough pseudocode, but it should get the idea across.

PROCEDURE A
    CURSOR
        CALL B -- I WANT TO SUPPRESS THE RESULTS FROM B
    END
    SELECT *
END
PROCEDURE B
    Do some interesting things
    SELECT *
END

As you can see above, A calls B and B does some things that I want and returns results that I don't care about. Once A is done, it returns its own set of results.

How do I suppress the results from B in A? I'm using SQL Server 2005. I would prefer not to make changes to B because it is working and more complex than I want to mess with.

Upvotes: 24

Views: 16482

Answers (3)

Peter Radocchia
Peter Radocchia

Reputation: 11007

Here's a light modification of proc A & B that would suit your needs:

PROCEDURE A
    CURSOR
        CREATE TABLE #__suppress_results (col1 int)
        CALL B -- I WANT TO SUPPRESS THE RESULTS FROM B
    END
    SELECT *
END
PROCEDURE B
    Do some interesting things
    IF OBJECT_ID('tempdb..#__suppress_results') IS NULL BEGIN
      SELECT *
    END
END

This avoids the problem of nested INSERT...EXEC, and preserves the existing behavior of proc B. On the off chance that some other calling proc already creates a temp table named #__suppress_results before calling proc B, just use a different name, like #__@supress_results.

And it works, because #__suppress_results is visible within proc B if proc B is called from proc A.

Upvotes: 1

Jim Birchall
Jim Birchall

Reputation: 350

Am I being really really stoopid but shouldn't "Do some interesting things" be in another procedure? Then Procedure A would call procedure C (which only does "Do some interesting things") and then do its required select and Procedure B could also call procedure C and do its select, rather than having the overhead of a second select and a temporary table that is only used as a dustbin?

Upvotes: 1

JoshBerke
JoshBerke

Reputation: 67068

You can try something like this:

/* Assume this table matches the output of your procedure */
DECLARE @tmpNewValue TABLE (newvalue int)
INSERT INTO @tmpNewValue 
EXEC ProcedureB

Upvotes: 36

Related Questions