On Aug 14, 10:54am, Mark Denham wrote:
} Subject: Re: SP's and SQLERRD ???
}
} Jon wrote
} >At 06:22 PM 8/10/95 GMT, June Tong wrote:
} >>Jon C. Vemo (jvemo@cyberspace.com) wrote:
} >>: Within a SP, I want to perform a select. After the select, I want to
} >>: determine whether any rows were found. According to TFM, using dbinfo to
} >get
} >>: 'sqlca.sqlerrd2' will return the number of rows processed. Only problem,
} it
} >>: doesn't return anything near accurate results.
} >I'm actually not as interested in the number of rows returned, but more
} >interested in trying to determine if ANY rows were returned from the query.
} >Since I can't use the STATUS variable in a SP, how can I determine whether
} >the query returned anything??
}
} If you have a column in the table that is NOT NULL then add this to the
} select [if it is not already].
}
} Then define a procedure variable LIKE the column from the table eg
}
} DEFINE acol LIKE atable.acolumn;
}
} You can then use the following code to determine no rows found
}
} BEGIN
} ON EXCEPTION IN ( -1225)
} .... your bits here .....
} END EXCEPTION [WITH RESUME]; -- [if you want procedure to
} continue]
}
} SELECT acolumn
} INTO acol
} FROM atable;
}
} END
}
} You can decide yourself whether you need the BEGIN/END block.
}
} hope this helps
}
} Mark Denham
There is another way of discovering whether a select in a SP returned anything:
define row_found char(1);
let row_found = "N"
foreach select ....
let row_found = "Y";
exit foreach;
end foreach;
if row_found =
This has the advantage of only returning the first page of results to the SP as
well as working for selects that return correct null values.
Cheers - Jim
--
-----------------------------------------------------------------------------
Jim Gordon DHL Airways Inc. jgordon@us.dhl.com
-----------------------------------------------------------------------------
My opinions are my own. They may vary with time but they remain mine!