Re: SP's and SQLERRD ???

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!