How To Turn off Stored Procedure Output

SQL Server 2008 R2. Running a stored procedure via a cursor-controlled loop from the SS Management Studio. (below). After 3,000 of 9,000 loops I receive an out of memory error. I believe this is related only to SS Mgmt Studio. So how do I shut off the output of both the calling routine (below) and the invoked SPROC? I am open to another approach to invoke the SPROC (EXECUTE POPULATE_EMA @sym_in, 20,50,100,12,26, @mink_in, @maxk_in;)

DELETE FROM TA_HISTORY

DECLARE tables_cursor CURSOR
   FOR
   SELECT symbol, MinDSeqKey, MaxDSeqKey
   FROM STOCK_VITALS;

OPEN tables_cursor;

DECLARE @sym_in NVARCHAR(10);
DECLARE @mink_in bigint;
DECLARE @maxk_in bigint;

FETCH NEXT FROM tables_cursor INTO @sym_in, @mink_in, @maxk_in;

WHILE (@@FETCH_STATUS <> -1)
BEGIN;
    --PRINT 'Now Processing. ' + @sym_in;
   EXECUTE POPULATE_EMA @sym_in, 20,50,100,12,26, @mink_in, @maxk_in;

   INSERT INTO TA_HISTORY(SYMBOL, DSEQKEY, EMA20, EMA50, EMA100, EMA12, EMA26)
   SELECT @sym_in, DSEQKEY, EMA1, EMA2,EMA3,EMA4,EMA5
   FROM temp_ema_data
   WHERE @maxk_in - @mink_in > 49

   FETCH NEXT FROM tables_cursor INTO @sym_in, @mink_in, @maxk_in;
END;

CLOSE tables_cursor;

DEALLOCATE tables_cursor;

** EDIT 5/18 - HERE IS THE SPROC - I NEED THE SPROC TO OPERATE ON 9,000+ ROWS IN ANOTHER TABLE.

BEGIN

DROP TABLE temp_ema_data

CREATE TABLE [dbo].[temp_ema_data](
    [n] [int] IDENTITY(1,1) NOT NULL,
    [dseqkey] [bigint] NULL,
    [close_price] [decimal](6, 2) NULL,
    [ema1] [decimal](8, 4) NULL,
    [ema2] [decimal](8, 4) NULL,
    [ema3] [decimal](8, 4) NULL,
    [ema4] [decimal](8, 4) NULL,
    [ema5] [decimal](8, 4) NULL
) ON [PRIMARY]

insert into temp_ema_data (dseqkey, close_price)
select dseqkey,prclose
from STOCK_HIST
where Symbol = @Symbol and dseqkey > @MinKey 
order by dseqkey asc

--declare variables needed
declare @K1 decimal(4,4), @K2 decimal(4,4), @K3 decimal(4,4), @K4 decimal(4,4)
, @K5 decimal(4,4)
declare @prev_ema_1 decimal(8,4), @prev_ema_2 decimal(8,4), @prev_ema_3 decimal(8,4)
, @prev_ema_4 decimal(8,4), @prev_ema_5 decimal(8,4),@initial_sma_1 decimal(8,4)
, @initial_sma_2 decimal(8,4), @initial_sma_3 decimal(8,2), @initial_sma_4 decimal(8,4)
, @initial_sma_5 decimal(8,4)
declare @anchor int

    set @K1 = 2/(1 + @ema_1_intervals + .000)
    set @K2 = 2/(1 + @ema_2_intervals + .000)
    set @K3 = 2/(1 + @ema_3_intervals + .000)
    set @K4 = 2/(1 + @ema_4_intervals + .000)
    set @K5 = 2/(1 + @ema_5_intervals + .000)           

select  @initial_sma_1 = avg(case when n < @ema_1_intervals 
        then close_price else null end),    
        @initial_sma_2  = avg(case when n < @ema_2_intervals 
        then close_price else null end),
        @initial_sma_3  = avg(case when n < @ema_3_intervals 
        then close_price else null end),
        @initial_sma_4  = avg(case when n < @ema_4_intervals 
        then close_price else null end),
        @initial_sma_5  = avg(case when n < @ema_5_intervals 
        then close_price else null end)                     
from temp_ema_data
where n < @ema_1_intervals or n < @ema_2_intervals or 
      n < @ema_3_intervals or n < @ema_4_intervals or
      n < @ema_5_intervals

update t1 
    set @prev_ema_1 = case 
    when n < @ema_1_intervals then null         
    when n = @ema_1_intervals then t1.close_price * @K1 + @initial_sma_1 * (1-@K1)  
    when n > @ema_1_intervals then t1.close_price * @K1 + @prev_ema_1 * (1-@K1) 
    end,
    @prev_ema_2 = case when n < @ema_2_intervals then null          
    when n = @ema_2_intervals then t1.close_price * @K2 + @initial_sma_2 * (1-@K2)  
    when n > @ema_2_intervals then t1.close_price * @K2 + @prev_ema_2 * (1-@K2)         
    end, 
    @prev_ema_3 = case when n < @ema_3_intervals then null          
    when n = @ema_3_intervals then 开发者_如何学Got1.close_price * @K3 + @initial_sma_3 * (1-@K3)  
    when n > @ema_3_intervals then t1.close_price * @K3 + @prev_ema_3 * (1-@K3)         
    end, 
    @prev_ema_4 = case when n < @ema_4_intervals then null          
    when n = @ema_4_intervals then t1.close_price * @K4 + @initial_sma_4 * (1-@K4)  
    when n > @ema_4_intervals then t1.close_price * @K4 + @prev_ema_4 * (1-@K4)         
    end, 
    @prev_ema_5 = case when n < @ema_5_intervals then null          
    when n = @ema_5_intervals then t1.close_price * @K5 + @initial_sma_5 * (1-@K5)  
    when n > @ema_5_intervals then t1.close_price * @K5 + @prev_ema_5 * (1-@K5)         
    end,            
    ema1 = @prev_ema_1, ema2 = @prev_ema_2, ema3 = @prev_ema_3, ema4 = @prev_ema_4,
    ema5 = @prev_ema_5, @anchor = n --anchor so that carryover works properly   
from temp_ema_data t1 with (TABLOCKX) OPTION (MAXDOP 1)

END

---

You can try under the menu item: Query->Query options...

On the tree select Results->Grid and Results->Text (whichever is applicable for you) there is a check box for "Discard results after execution". I haven't used it, but it sounds like it might do what you need.

EDIT: A quick test shows that this discards both query results as well as PRINT statement output. Also, a loop of 100 calls to a simple stored procedure went from taking several seconds (mostly time spent displaying the results) to pretty much an instant run time. I think this is what you're looking to find.

---

Did you try adding the following to the top of you SPROC:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET NOCOUNT ON

---

Based on the fact that cursor's are resource hogs, if cursors isn't a requirement, I'd suggest using sql server table variables as an alternative.

Also look at this link for conversion of cursor based procedure to a table variable based procedure.

---

As @Mark Kram points out, SET NOCOUNT ON is one thing you need, to prevent output from the sproc you showed us.

For the called sproc POPULATE_EMA, there is no way for us to know what it outputs based on the details you've provided - are you getting resultsets returned to SSMS?

If you are getting one resultset returned (to the client) per call to POPULATE_EMA, you may be able to INSERT INTO a temp table, on the server-side, to avoid that table being sent to the client. This would look something like:

DELETE FROM TA_HISTORY

--This table would need to match the structure of POPULATE_EMA!
CREATE TABLE #TempResults (Column1 Int, Column2 Int) --, etc

DECLARE tables_cursor CURSOR
   FOR
   SELECT symbol, MinDSeqKey, MaxDSeqKey
   FROM STOCK_VITALS;

OPEN tables_cursor;

DECLARE @sym_in NVARCHAR(10);
DECLARE @mink_in bigint;
DECLARE @maxk_in bigint;

FETCH NEXT FROM tables_cursor INTO @sym_in, @mink_in, @maxk_in;

WHILE (@@FETCH_STATUS <> -1)
BEGIN;
    --PRINT 'Now Processing. ' + @sym_in;
   INSERT INTO #TempResults
   EXECUTE POPULATE_EMA @sym_in, 20,50,100,12,26, @mink_in, @maxk_in;

   INSERT INTO TA_HISTORY(SYMBOL, DSEQKEY, EMA20, EMA50, EMA100, EMA12, EMA26)
   SELECT @sym_in, DSEQKEY, EMA1, EMA2,EMA3,EMA4,EMA5
   FROM temp_ema_data
   WHERE @maxk_in - @mink_in > 49

   FETCH NEXT FROM tables_cursor INTO @sym_in, @mink_in, @maxk_in;
END;

CLOSE tables_cursor;

DEALLOCATE tables_cursor;