Fetch SQL Server stored procedure output results into table

Question

I have tried some solutions from the internet but they are not working for me .

My task is to get the out put of stored procedure into a table.The data is being inserted inside a cursor by loop . I am creating temporary table to store and display the data.

My code:

ALTER procedure [dbo].[usp_Test]
AS
   SET NOCOUNT ON;

   declare @caseId int;
   declare  @CHG_ID int;
   declare @HEAR_ID int;

   SET @CHG_ID = 1
   set @testid = 1;

   DECLARE db_cursor CURSOR FOR 
     SELECT C_CASE_ID
     FROM table1     // tHERE WILL BE MULTIPLE CASEIDS    

   -- here I am trying to delete the temporary table, but it does not work
   IF OBJECT_ID('tempdb..##test_temp_table') IS NOT NULL 
      TRUNCATE TABLE ##test_temp_table
   ELSE
      CREATE TABLE test_temp_table(HEAR_ID int)

   OPEN db_cursor 

   FETCH NEXT FROM db_cursor INTO @caseId 

   WHILE @@FETCH_STATUS = 0   
   BEGIN    
      insert into test_temp_table 
        EXEC STOREDPROCTEST2 @caseId, 1, @HEAR_ID OUTPUT;

      -- LOOP THROUGH THE CURSOR TO GET ALL     CASE IDS
      FETCH NEXT FROM db_cursor INTO @caseId 

      SELECT HEAR_ID FROM test_temp_table;
   END   

   CLOSE db_cursor  
   DEALLOCATE db_cursor;

I have two issues:

I cannot delete the temporary table
I am not seeing any output from the temporary table

As a starting observation, you mostly are referring to test_temp_table, which isn't a temporary table no matter what you name it, and despite the fact that you check for a global temp table named ##test_temp_table using that OBJECT_ID() statement. Which are you trying to do? — AHiggins
– AHiggins, Commented May 14, 2015 at 17:18

AHiggins · Accepted Answer · 2015-05-14 17:37:16Z

1

[##test_temp_table] and [test_temp_table] are two different tables. First one is a global temp table, second one is a user table. I believe you want to replace the user table with the global temp table, i.e. replace object [test_temp_table] with [##test_temp_table]. or vice versa. In the end, you have to ensure you are querying the correct table.

edited May 14, 2015 at 17:37

AHiggins

7,2357 gold badges38 silver badges56 bronze badges

answered May 14, 2015 at 17:35

Greg

4,1255 gold badges35 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2465036 Over a year ago

AHiggins ,can you also show how to insert the data into the table from the stored proc

Collectives™ on Stack Overflow

Fetch SQL Server stored procedure output results into table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related