title: LEAD over a Cursor variable
body: <p>I am attempting to get the next record in the cursor, without moving it. i.e Something like the peek function. Here is some sql code that I have written so far:</p>
  
  <pre><code>DECLARE
      @Id     varchar(80),
      @Name   varchar(80),
      @NextId varchar(80)
  
  BEGIN
  
      SET @MyCursor = CURSOR FOR
      SELECT id,name FROM dbo.My_TABLE
      WHERE name like '%joe%';
  
      OPEN @MyCursor 
          FETCH NEXT FROM @MyCursor 
      INTO @Id, @Name
  
      WHILE @@FETCH_STATUS = 0
      BEGIN
          print 'Id is :: ' + cast(@Id as varchar(80)) + ' : ' + @Name
          SELECT @NextId=id, LEAD (id,1) OVER (ORDER BY id) FROM @MyCursor;
          print 'The next record is :: ' + @NextId
          FETCH NEXT FROM @MyCursor 
          INTO @Id, @Name
      END; 
  </code></pre>
  
  <p>However, I get an error stating that:</p>
  
  <pre><code>The variable '@MyCursor' is a cursor variable, but it is used in a place where a cursor variable is not valid.
  </code></pre>
  
  <p>When I issue the select statement, I might get some data like this back</p>
  
  <pre><code>----+------
  ID    NAME
  ----+------
  5   + Joes
  6   + FakeJoe
  7   + Joes
  8   + Joes
  9   + MikeJoes
  </code></pre>
  
  <p>for me, it is important to compare ID 5 and 6 to see if the name matches. If it does, like 7 and 8, I want to delete the latter record. If it does not, like 5 and 6, I want to leave the records alone</p>