How to Suppress the SELECT Output of a Stored Procedure called from another Stored Procedure in SQL Server?

sql

performance

sql-server

output-suppression

byAnton Shumikhin·Jan 20, 2025

Here's the quick method to prevent the output of an inner stored procedure from being displayed. Use a temporary table to redirect and capture this output. If coding isn't your strong suit, think of this as pushing an 'invisible mode' button on your procedure's selector!

-- Define inner SP that generates noisy output
CREATE PROCEDURE InnerSP AS SELECT 'Sssh! I am supposed to be invisible!';

-- Define outer SP to hush the noisy InnerSP
CREATE PROCEDURE OuterSP AS
BEGIN
    CREATE TABLE #Temp (Col VARCHAR(255)); -- Whisper-space for output
    INSERT INTO #Temp EXEC InnerSP; -- Redirect InnerSP's talk to whisper-space
END;

Running OuterSP now will be a serene experience, this dialog will stay out of your sight!

The hush trio: Dynamic SQL, Temporary Tables, and the noise-leash option

Dynamic SQL and Temporary Tables: The silent workers

Dynamic SQL twinned with temporary tables can do wonders tackling overflowing data, especially bulky and verbose XML that spills everything the moment it's let free. Now, you can turn Big-mouthed Bob's chattering into pin-drop silence.

-- Use dynamic SQL within the outer SP
CREATE PROCEDURE OuterSPDynamic AS
BEGIN
    -- Store the dynamic SQL command string
    DECLARE @sqlCommand NVARCHAR(MAX) = 'INSERT INTO #TempTable EXEC InnerSP';
    CREATE TABLE #TempTable (Col XML); -- Whisper space for XML gossip

    EXEC sp_executesql @sqlCommand; -- Execute the dynamic command, not the gossip
END;

Looping, the smart worker

When the need is for frequent calls to sub-procedure, consider looping with a "top 1 record" strategy, it's like having a "noise-leash" on your procedures. Take only what you need, keep gossip out!

-- Loop through calls to the over-talkative inner SP, pull out meaningful things
CREATE PROCEDURE OuterSPLoop AS
BEGIN
    CREATE TABLE #TempLoop (Col INT);  -- Keep calm and loop on!
    WHILE (Some juicy gossip here)    -- Condition for looping
    BEGIN
        INSERT INTO #TempLoop (Col)
        SELECT TOP 1 * FROM OPENROWSET('SQLNCLI', 'Server=MyServer;Trusted_Connection=yes;',
                                       'EXEC Database.Schema.InnerSP');  -- Just in: Top news!
    END;
END;

Total shutdown: Touch not the data!

At times when your need is to silence the procedure like a total media blackout, use a discard table or a dummy variable. Who said we don't believe in miracles when we can make data disappear!

-- Use a dummy variable
CREATE PROCEDURE SuppressOutput AS
BEGIN
    DECLARE @DummyResult TABLE (Col XML);  -- The data black-hole
    INSERT INTO @DummyResult EXEC InnerSP;  -- Don't let the world know!
END;

In this quiet world, ensure you have a dummy as small as a dot, avoid memory clutter!

Advanced noise control methods and their use-case scenario

Dodge the noise: Save to File

When bulk XML data threatens to degrade performance in SQL Server Management Studio (SSMS), redirect that output storm to a file:

Execute your usual noisy command in SSMS.
Use CTRL+SHIFT+F to point to a land far far away: the file path.
Run the command, watch SSMS thank you as the noise bypasses it straight to the file.

Long-term solution: The Engineers' choice

Immediate fixes are great, but what's better? A long-term solution like modifying or splitting the original stored procedure:

Bi-partisan policy- Splitting the procedure: Create sub-teams, make each one work on a piece of the procedure. Less gossip, better control.
Modify to control noise: Add parameters that control the output flow. It's like having your own content curating team!

File Redirection - A Favored tool: In SSMS, redirecting output keeps the grid cleaner and smoother, no more performance jams due to traffic build-up! Go ahead, set default options in SSMS for results to be sent straight to a file.

Extra Tips

A good output buffering technique can sail your client application smoothly through the data storm:

Huge XML Data is often a pain. Carefully redirect it to temporary storage or smartly extract only what's needed.
Learn from the community: Dive into developer forums, immerse yourself in the ocean of knowledge provided by experienced divers like you.

explain-codes / Sql / How to Suppress the SELECT Output of a Stored Procedure called from another Stored Procedure in SQL Server?

Linked

Passing an array of parameters to a stored procedure



Declare Variable for a Query String



Exec failed because the name not a valid identifier?



How to execute a stored procedure inside a select query



Execute STORED PROCEDURE FROM A FUNCTION

