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. >> >>Just as in 4GL & ESQL/C, "number of rows processed" really applies only to >>UPDATEs and DELETEs, not to SELECTs. For SELECT's it's pretty much the >>same >>as the sqexplain.out "Estimated Rows Returned". I'm afraid that the two >>options for figuring out number of rows selected are the same two there've >>always been: FETCH all the rows and count them, or SELECT COUNT(*) >June - >I don't mean to sound rude, but RTFM. Guide to SQL:Syntax, version 7.1, pg >1-568, bottom of page, section entitled "Using the 'sqlca.sqlerrd2' Option", >first two paragraphs. I quote; >"The 'sqlca.sqlerrd2" option returns a single integer that provides the >number of rows processed by SELECT, INSERT, DELETE, UPDATE, and >EXECUTE PROCEDURE statements." >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 BBC London, UK Mark.Denham@bbc.co.uk