title: SQL while loop to find the next row based on name
body: <p>I have an SQL trigger where whenever a new customer is checked in they are auto assigned to a technician. I have it working great with a case statement like this</p>
  
  <pre><code>SET @TechOrder = 
  (
      CASE
          WHEN @LastTech = 'Tech1' THEN 'Tech2'
          WHEN @LastTech = 'Tech2' THEN 'Tech3'
          WHEN @LastTech = 'Tech3' THEN 'Tech4'
          WHEN @LastTech = 'Tech4' THEN 'Tech1'
          and so on...
          ELSE 'Tech1'
      END
  )   
  </code></pre>
  
  <p>Then it updates the inserted order with this            </p>
  
  <pre><code>UPDATE WorkOrder
  SET Technician = @TechOrder
  WHERE ID = @OrderID
  </code></pre>
  
  <p>There is a lot of job roll changes and turn around for this position and I don't want to go in and manually change the SQL trigger every time there is a new tech. I was wondering if there is a better way to assign a tech based on the employee table WHERE Job_Roll equals this position. </p>
  
  <p>I was thinking a loop or cursor may work to select the next tech in line, but am not sure how I would do this. I am using SQL 2008 R2 so the use of LAG/LEAD won't work.</p>
  
  <p>Any help would be appreciated.</p>