title: How to Suppress the SELECT Output of a Stored Procedure called from another Stored Procedure in SQL Server?
body: <p>I'm not talking about doing a "SET NOCOUNT OFF".  But I have a stored procedure which I use to insert some data into some tables.  This procedure creates a xml response string, well let me give you an example:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[insertSomeData] (@myParam int) AS
  DECLARE @reply varchar(2048)
  
  ... Do a bunch of inserts/updates...
  
  SET @reply = '&lt;xml&gt;&lt;big /&gt;&lt;outputs /&gt;&lt;/xml&gt;'
  SELECT @reply
  GO
  </code></pre>
  
  <p>So I put together a script which uses this SP a bunch of times, and the xml "output" is getting to be too much (it's crashed my box once already).  </p>
  
  <p>Is there a way to suppress or redirect the output generated from this stored procedure?  I don't think that modifying this stored procedure is an option.</p>
  
  <p>thanks.</p>
  
  <hr>
  
  <p>I guess i should clarify.  This SP above is being called by a T-SQL Update script that i wrote, to be run through enterprise studio manager, etc.</p>
  
  <p>And it's not the most elegant SQL i've ever written either (some psuedo-sql):</p>
  
  <pre><code>WHILE unprocessedRecordsLeft
    BEGIN
      SELECT top 1 record from updateTable where Processed = 0
      EXEC insertSomeData @param = record_From_UpdateTable
    END
  </code></pre>
  
  <p>So lets say the UpdateTable has some 50k records in it.  That SP gets called 50k times, writing 50k xml strings to the output window.  It didn't bring the sql server to a stop, just my client app (sql server management studio).</p>