title: CURSOR with a stored procedure with OUTPUT parameters
body: <p>I have to insert one record at a  time into a table and then execute a stored procedure by passing output variables.</p>
  
  <p>The insert part actually does do the this and I see two different records into the destination table. But the stored procedure seems to use the same out parameters that were passed for the very first record inserted into destination table. So basically when a stored procedures is being called in loops over the same output parameters over and over again for each distinct ID that is being inserted into the destination table.</p>
  
  <p>In my pseudocode below it prints 3 times the following 5, 10, 15 . Which is correct since it takes each new ID in the <code>dbo.Table_Test</code>. But in my actual code actually it does take only only very first ID that repeats looping over the same ID three times.</p>
  
  <pre><code>-------- CREATING STORED PROCEDURE --------
  USE MyDB;
  GO
  
  DROP PROCEDURE IF EXISTS dbo.sp_Testing
  
  USE MyDB;
  GO
  
  CREATE PROCEDURE dbo.sp_Testing
      @QueueId INT,
      -- response
      @MainId INT OUT,  -- this allows null
      @MessageTx VARCHAR(500) OUT,
      @SuccessIn BIT OUT
  AS
  BEGIN
      SET NOCOUNT ON;
  
      IF NOT EXISTS (SELECT * FROM MyDB.sys.tables WHERE name = 'Table_Test') --print 1
  
      CREATE TABLE dbo.Table_Test 
      (
          ID INT NOT NULL PRIMARY KEY IDENTITY(5,5),
          name VARCHAR(10) NULL,
          Phone INT NULL,
          category VARCHAR(10) NULL
      )
  
      INSERT INTO dbo.Table_Test (name)
      VALUES ('Andrew')
  
      SET @MainId = SCOPE_IDENTITY()
      PRINT @MainId
  END
  
  -------- END OR STORED PROCEDURE --------
  GO
  
  -------- INSERT STATEMENTS ---------
  USE MyDB;
  
  IF OBJECT_ID('tempdb..#MainTable') IS NOT NULL
      DROP TABLE #MainTable
  
  IF OBJECT_ID('tempdb..#Queue') IS NOT NULL
      DROP TABLE #Queue
  
  DECLARE @MessageTx VARCHAR(30)
  DECLARE @SuccessIn BIT
  DECLARE @QueueId INT
  DECLARE @MainId INT
  DECLARE @ParentId INT
  
  SET @MainId = NULL
  SET @SuccessIn = 1
  
  CREATE TABLE #MainTable 
  (
          ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
          name VARCHAR(10) NULL,
          Phone INT NOT NULL,
          category VARCHAR(10) NULL
  )
  
  INSERT INTO #MainTable (name, Phone, category)
  VALUES ('Adam', 123433, 'new'),
         ('John', 222222, 'new'),
         ('Samuel', 123123313, 'new')
  
  -- SELECT * FROM #MainTable 
  -- SELECT * FROM  #Queue
  -- SELECT * FROM  #Test
  
  DECLARE Cursor_test CURSOR LOCAL FOR  
      SELECT id
      FROM #MainTable         
  
  -- get relationships for next level 
  OPEN Cursor_test  
  
  FETCH NEXT FROM Cursor_test INTO @ParentId   
  
  WHILE @@FETCH_STATUS = 0   
  BEGIN
      IF OBJECT_ID('tempdb..#Queue') IS NOT NULL
          DROP TABLE #Queue
  
      CREATE TABLE #Queue
      (
          PK INT NOT NULL PRIMARY KEY identity (3,2), 
          ID INT NOT NULL
      )
  
      INSERT INTO #Queue (id)
          SELECT id
          FROM #MainTable
  
      SET @QueueId = SCOPE_IDENTITY()
  
      -- real-time creation
      EXEC dbo.sp_Testing @QueueId, @MainId, @MessageTx OUT, @SuccessIn OUT
  
      FETCH NEXT FROM Cursor_test INTO @ParentId
  END
  
  CLOSE Cursor_test   
  DEALLOCATE Cursor_test
  </code></pre>