title: Using row count from a temporary table in a while loop SQL Server 2008
body: <p>I'm trying to create a procedure in SQL Server 2008 that inserts data from a temp table into an already existing table. I think I've pretty much figured it out, I'm just having an issue with a loop. I need the row count from the temp table to determine when the loop should finish. </p>
  
  <p>I've tried using @@ROWCOUNT in two different ways; using it by itself in the WHILE statement, and creating a variable to try and hold the value when the first loop has finished (see code below). </p>
  
  <p>Neither of these methods have worked, and I'm now at a loss as to what to do. Is it possible to use @@ROWCOUNT in this situation, or is there another method that would work better?</p>
  
  <pre><code>CREATE PROCEDURE InsertData(@KeywordList varchar(max))
  AS
  BEGIN
  
  --create temp table to hold words and weights
  CREATE TABLE #tempKeywords(ID int NOT NULL, keyword varchar(10) NOT NULL); 
  
  DECLARE @K varchar(10), @Num int, @ID int
  
  SET @KeywordList= LTRIM(RTRIM(@KeywordList))+ ','
  SET @Num = CHARINDEX(',', @KeywordList, 1)
  SET @ID = 0
  
  --Parse varchar and split IDs by comma into temp table
  IF REPLACE(@KeywordList, ',', '') &lt;&gt; ''
  BEGIN
      WHILE @Num &gt; 0
      BEGIN
          SET @K= LTRIM(RTRIM(LEFT(@KeywordList, @Num - 1)))
          SET @ID = @ID + 1
          IF @K &lt;&gt; ''
          BEGIN
              INSERT INTO #tempKeywords VALUES (@ID, @K) 
          END
          SET @KeywordList = RIGHT(@KeywordList, LEN(@KeywordList) - @Num)
          SET @Num = CHARINDEX(',', @KeywordList, 1)
          --rowcount of temp table
          SET @rowcount = @@ROWCOUNT
      END
  END
  
  --declaring variables for loop
  DECLARE @count INT
  DECLARE @t_name varchar(30)
  DECLARE @key varchar(30)
  DECLARE @key_weight DECIMAL(18,2)
  --setting count to start from first keyword
  SET @count = 2
  --setting the topic name as the first row in temp table
  SET @t_name = (Select keyword from #tempKeywords where ID = 1)
  --loop to insert data from temp table into Keyword table
  WHILE(@count &lt; @rowcount)
      BEGIN
          SET @key = (SELECT keyword FROM #tempKeywords where ID = @count)
          SET @key_weight = (SELECT keyword FROM #tempKeywords where ID = @count+2)
          INSERT INTO Keyword(Topic_Name,Keyword,K_Weight)
          VALUES(@t_name,@key,@key_weight)
          SET @count= @count +2
      END 
  --End stored procedure  
  END
  </code></pre>