title: How do I pass a parameter in a stored procedure looping values and dump the result set into a temp table?
body: <p>How do I create a stored procedure which can be passed a <code>@CandidatelistID</code> as a parameter to the existing stored procedure logic below.</p>
  
  <p>I am looping the <code>CandidateID</code> and <code>CandidateListID</code> and running a stored procedure <code>usp_ApplyRankingRules</code> inside the loop to get the <code>Weightage</code> for each of them.</p>
  
  <p>Then instead of insert and update the values in a table - <code>RPT_CandidateCardReport</code> I want to load the same fields in a temp table. When I am doing that I am getting the following error -</p>
  
  <pre><code>(0 row(s) affected)
  
  (0 row(s) affected)
  Msg 208, Level 16, State 0, Procedure usp_RPT_CandidateCardReporttest, Line     24 [Batch Start Line 53]
  Invalid object name '#tempRPTCandidateCardReport'.
  </code></pre>
  
  <p>The data is present in the tables I am trying to loop and should show display the result from the temptable select statement at the bottom..</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[usp_RPT_CandidateCardReporttest]     @candidateListIDs varchar(36)
      AS
      BEGIN
  declare @candidateListID varchar(36)
  declare @candidateID varchar(36)
  declare @var_weightage decimal
  declare @listcount INT = (SELECT COUNT(1) FROM CandidateList_Candidates)
  declare @loopcount INT = 1
  
  
  IF OBJECT_ID('tempdb..#tmpListCandidate') IS NOT NULL DROP TABLE #tmpListCandidate
  SELECT CandidateListID, CandidateID, ROW_NUMBER()OVER(ORDER BY cl.CandidateID) AS Id INTO #tmpListCandidate 
  FROM CandidateList_Candidates cl
  where CandidateListID = @candidateListIDs
  
  WHILE(@loopcount &lt;= @listcount)
      BEGIN
          SET @candidateListID = (SELECT CandidateListID FROM #tmpListCandidate WHERE Id = @loopcount)
          SET @candidateID = (SELECT CandidateID FROM #tmpListCandidate WHERE Id = @loopcount)
  
          EXEC usp_ApplyRankingRules @candidateListID, @candidateID, @var_weightage OUTPUT
  
          IF EXISTS (SELECT 1 FROM #tempRPTCandidateCardReport WHERE listID = @candidateListID AND candidateID = @candidateID)
              BEGIN
                  UPDATE #tempRPTCandidateCardReport SET weightage = @var_weightage WHERE listID = @candidateListID AND candidateID = @candidateID
              END
          ELSE
              BEGIN    
                  INSERT INTO #tempRPTCandidateCardReport (listID, candidateID, weightage)
                      VALUES (@candidateListID, @candidateID, @var_weightage) 
              END
          SET @loopcount = @loopcount + 1
      END
  
  select listid,candidateid,weightage from #tempRPTCandidateCardReport
  
  IF OBJECT_ID('tempdb..#tmpListCandidate') IS NOT NULL 
  BEGIN 
      DROP TABLE #tmpListCandidate
      DROP TABLE #tempRPTCandidateCardReport
  END
  </code></pre>
  
  <p>END
  GO</p>