title: What is the point of using a cursor in a Sybase stored procedure for a simple 1 row UPSERT?
body: <p>I am looking to re-write a legacy Sybase stored procedure that's 20 years old.</p>
  
  <p>The logic seems pretty simple (an UPSERT of a <strong><em>single row</em></strong> into a table, with the only wrinkle being that unique key for table lookups can be 2 or 3 columns, based on input parameter).</p>
  
  <p>The problem is, the stored procedure accomplishes this by declaring a cursor and using that instead of simply doing an UPSERT (in 2 versions - 2/3 column ones - based on an IF statement).</p>
  
  <p>Is there any reason to use the cursor? Or was this just the original long-gone-developer being too fancy for no good reason?</p>
  
  <p><em>In case it matters, the details:</em></p>
  
  <p>Table has key-value pairs, stored for multiple entities. Each key can either be multi-valued or single-valued (which is in a lookup key list table, and is outside the scope of this question). Each record has an active flag:</p>
  
  <pre><code>entity_id int  -- foreign key into entity table
  unique_id int  -- identity column for this table
  key       varchar(20)
  value     varchar(255)
  active    char
  comment   varchar(20) -- Change control audit purposes.
  </code></pre>
  
  <p>Stored procedure accepts <code>@entity_id, @key, @value, @active and @comment</code> as input parameters.</p>
  
  <p>Unique key for the table is:</p>
  
  <ul>
  <li><code>entity_id + key</code>, for keys that are single-valued</li>
  <li><code>entity_id + key + value</code>, for keys that are multiple-valued</li>
  </ul>
  
  <p>The stored procedure does the upsert - if the table already has the row as defined by the unique key, it updates the active flag (and the setting value, for single-valued keys).</p>
  
  <p>If the table does not have the row as defined by unique key, it gets inserted. </p>
  
  <p>The way I would do it would be simple</p>
  
  <pre><code>DECLARE @unique_id int
  IF (@key_type="M") -- multi-valued
  BEGIN
     SELECT @unique_id = unique_id
     FROM   my_table
     WHERE  entity_id = @entity_id
      AND   key       = @key
      AND   value     = @value
  
     IF (@unique_id IS NULL) BEGIN
         -- INSERT
     END
     ELSE BEGIN
         UPDATE my_table
         SET    active  = @active
               ,comment = @comment
         WHERE  entity_id = @entity_id
          AND   key       = @key
          AND   value     = @value
      END
  END
  ELSE -- single-valued
  BEGIN
     SELECT @unique_id = unique_id
     FROM   my_table
     WHERE  entity_id = @entity_id
      AND   key       = @key
  
     IF (@unique_id IS NULL) BEGIN
         -- INSERT
     END
     ELSE BEGIN
         UPDATE my_table
         SET    active = @active
               ,comment = @comment
               ,value  = @value 
         WHERE  entity_id = @entity_id
          AND   key       = @key
      END
  END
  </code></pre>
  
  <p>Instead, the stored procedure does this:</p>
  
  <pre><code>IF (@key_type="M") BEGIN -- multi-valued
      DECLARE my_cursor CURSOR
          SELECT active FROM my_table
          WHERE  entity_id = @entity_id
          AND   key       = @key
  -- next line is the ONLY difference between single and multiple value cursor
          AND   value     = @value 
          FOR UPDATE OF value, active, comment
  END
  ELSE BEGIN -- Single-valued
      DECLARE preexisting CURSOR
          SELECT active FROM my_table
          WHERE  entity_id = @entity_id
          AND   key       = @key
          FOR UPDATE OF value, active, comment
  END
  </code></pre>
  
  <p>And then does upsert using the cursor:</p>
  
  <pre><code>open my_cursor
  
      DECLARE @cactive CHAR
      FETCH my_cursor INTO @cactive
  
      if ( @@sqlstatus = 0 )
          UPDATE my_table
          SET    active = @active
                ,comment = @comment
                ,value  = @value 
          WHERE current of my_cursor 
      ELSE
          -- INSERT (no cursor use, same insert I would do)
  
  close my_cursor
  deallocate cursor my_cursor
  </code></pre>