Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Michael Peppler <mpeppler at MBAY dot NET>
Subject: Error when checking "if exists"
Date: May 26 1998 6:30PM

Rashmi Anand writes:
 > Hello, 
 > I am trying to check for the existance of a row and if it exists, send
 > an update otherwise, send an Insert statement:
 > if exists (select * from ... where ...)
 >    begin 
 >      Update ... where ...
 >    end
 > else
 >    begin 
 >      Insert ...
 >    end
 > and I get the following error:
 > DB-Library error: Attempt to initiate a new SQL Server operation with
 > results pending.

This needs to go into the FAQ.

You should *always* call dbresults() in a loop, until it returns
NO_MORE_RESULTS. Your query returns at least two sets of results, even 
if it doesn't return any *rows*.

As an aside you should probably code the above like this:

update ... where
if @@rowcount = 0
  insert ....

This avoids an extra select on the table (the update fails silently if 
the where clause does not match any rows).

Michael Peppler         -||-  Data Migrations Inc.    -||-
Int. Sybase User Group  -||-