title: How to do while loop with if exist in SQL Server stored procedure?
body: <p>I'm just learning to use while loop in stored procedure I'm not sure why the stored procedure does not return any value.  </p>
  
  <p>I'm using the if exist to check whether the id exist in the table or not.
  And if there is more than 1 id I need to execute again the same query.  </p>
  
  <p>I tried using while loop and its not working.</p>
  
  <p>I declare the <code>@keyValue</code> as <code>Varchar</code> and it causes an error </p>
  
  <blockquote>
    <p>Conversion failed when converting the varchar value 'c29dc109b310479fa5c281eb20c61656' to data type int.</p>
  </blockquote>
  
  <p>That is why I use <code>COUNT</code> at the <code>WHILE</code></p>
  
  <pre><code>IF EXISTS (SELECT t1.ref_id 
             FROM pbosproinvoiceitem t1 
             WHERE t1.ref_id = @keyValue)
      WHILE COUNT(@keyValue) &gt; 1
      BEGIN
          --need to do for loop
          SELECT @balWghtSso = (SELECT t1.act_wght  
                                FROM ccossoitem t1 
                                WHERE t1.master_id = @keyValue) - 
                               (SELECT t1.wght 
                                FROM ccossoitem t1 
                                WHERE t1.master_id = @keyValue)
          SELECT @balWghtBl = (SELECT t1.act_wght 
                               FROM ccosblitem t1 
                               WHERE t1.master_id = @keyValue) - 
                              (SELECT t1.wght 
                               FROM ccosblitem t1 
                               WHERE t1.master_id = @keyValue)
  
          SELECT DISTINCT 
              t1.debtor_code AS customer_code, t1.tml_code, t1.scn, 
              t2.cg_type, t3.qty, @balWghtSso AS mea, t3.vol,
              CASE 
                 WHEN t3.from_loc = 'DL' THEN 1 ELSE 0 
              END AS ex_wharf,
              t2.dg_flg, 0 AS conveyor_ind, t1.sso_num AS ref_doc_num,
              t1.id AS ref_id
          FROM 
              ccosso t1
          INNER JOIN 
              ccossoitem t2 ON t1.id = t2.master_id
          INNER JOIN 
              ccosgodown t3 ON t3.blso_id = t1.id AND t3.blso_item_id = t2.id
          WHERE 
              t3.move_type = 'LOAD'
              AND t1.id = @keyValue
  
          UNION ALL
  
          SELECT DISTINCT 
              t1.debtor_code AS customer_code, t1.tml_code, t1.scn, 
              t2.cg_type, t3.qty, 
              @balWghtBl AS mea, t3.vol, 
              CASE 
                 WHEN t3.from_loc = 'DD' THEN 1 ELSE 0 
              END AS ex_wharf,
              t2.dg_flg, 0 AS conveyor_ind, t1.bl_num AS ref_doc_num,
              t1.id AS ref_id
          FROM 
              ccosbl t1
          INNER JOIN 
              ccosblitem t2 ON t1.id = t2.master_id
          INNER JOIN 
              ccosgodown t3 ON t3.blso_id = t1.id AND t3.blso_item_id = t2.id
          WHERE 
              t3.move_type = 'DELV'
              AND t1.id = @keyValue
      END;
  </code></pre>