|
We have a stored procedure with many deletes of physical records and if one is not found the procedure fails out but just because that one is not present does not mean the others following are not. I have been looking at ASA online documentation and found ALTER PROCEDURE DBA.SPN00_DeleteIncident(@incidentNumber integer, @exposureNumber integer) AS Begin ..... delete from table1 where ...... ..... delete from table2 where ...... -- for example if this fails it will not continue to next ..... delete from table3 where and so on So looking documentation it had a create and then said alter is the same syntax on line 2 with AS, I remove AS and then Syntax error on BEGIN. I am trying ALTER PROCEDURE DBA.SPN00_DeleteIncident(@incidentNumber integer, @exposureNumber integer) ON EXCEPTION RESUME AS Begin etc. etc. Once this is resolved do I have to handle each delete with something or does the procedure just keep on continuing? |
|
Take a look at the documentation carefully. SQL Anywhere supports two dialects of SQL, Watcom-SQL and Transact SQL (ASE's stored procedure language) and the two are different - there are different CREATE PROCEDURE statements (and correspondingly ALTER PROCEDURE statements) for each dialect. If you use AS with CREATE PROCEDURE, the SQL grammar for Transact-SQL is used. |
|
I guess you're mixing Watcom-SQL and T-SQL syntax - and ON EXCEPTION RESUME is Watcom-SQL syntax, whereas the "CREATE PROCEDURE ... AS statement-list" is T-SQL. Watcom-SQL uses "CREATE PROCEDURE ... BEGIN ... END" syntax (i.e. no AS). So simply use ALTER PROCEDURE DBA.SPN00_DeleteIncident(@incidentNumber integer, @exposureNumber integer) ON EXCEPTION RESUME BEGIN ... END; FWIW, what do you mean by a failing DELETE? A delete on a non-existing row is no error, it's just a no-op. So what kind of errors are you hitting? got the syntax to work with your suggestions but still no working as I want.
(13 Jun '12, 11:48)
clayton
Replies hidden
Still it would be helpful to know what exactly error/warning message you are getting? - IMHO, a delete on an non-existing row would simply be an no-op, and the control flow would surely reach the next station without any error-handling. Are the rows from different tables linked by foreign key relationships?
(13 Jun '12, 11:56)
Volker Barth
My link appeared to not work so here are my comments and code below. Sorry and thanks Code is attached for example. I have some print commands in the flow. The example I am trying is in the first block and the first print is displayed in the Sybase Database window. The print below that delete from nf_mod11_data where i_incident_id = @incidentNumber; print 'Inside delete block 3 last.\x0A'; -- 06132012 kcp added for new table delete from nf_mod01_basic_otherdata where i_incident_id = @incidentNumber is never seen and I have data there. After the procedure runs it deletes the first 3 delete rows which have data and are present but never gets to the last one. I move the last one right below the nf_exposure and it removes the record. The rows after nf_exposure may or not be present but needs to keep on falling through.[link ALTER procedure DBA.SP_N00_DeleteIncident(in @incidentNumber integer,in @exposureNumber integer) on exception resume begin declare @count integer; declare @xstring varchar(100); declare @dtExport datetime; print 'BEGIN DELETION.\x0A'; select dt_last_exported into @dtExport from nf_exposure where i_incident_id = @incidentNumber and i_exposure_id = 0; --if dt_last_exported is null for exposure = 0, --delete this incident/exposure. print 'Checking for NULL export date and incident = 0.\x0A'; if @dtExport is null and @exposureNumber = 0 then print 'Inside delete block 1.\x0A'; delete from nf_transaction where i_incident_id = @incidentNumber; delete from nf_incident where i_incident_id = @incidentNumber; delete from nf_exposure where i_incident_id = @incidentNumber; delete from nf_mod01_address_data where i_incident_id = @incidentNumber; delete from nf_mod01_aid_data where i_incident_id = @incidentNumber; delete from nf_mod01_aid_detail_data where i_incident_id = @incidentNumber; delete from nf_mod01_basic_data where i_incident_id = @incidentNumber; delete from nf_mod01_member_data where i_incident_id = @incidentNumber; delete from nf_mod01_oic_data where i_incident_id = @incidentNumber; delete from nf_mod01_remarks_data where i_incident_id = @incidentNumber; delete from nf_mod01K1_data where i_incident_id = @incidentNumber; delete from nf_mod01K2_data where i_incident_id = @incidentNumber; delete from nf_mod02_data where i_incident_id = @incidentNumber; delete from nf_mod02_equipment_data where i_incident_id = @incidentNumber; delete from nf_mod02_mobile_data where i_incident_id = @incidentNumber; delete from nf_mod03_data where i_incident_id = @incidentNumber; delete from nf_mod04_data where i_incident_id = @incidentNumber; delete from nf_mod05_data where i_incident_id = @incidentNumber; delete from nf_mod05K_data where i_incident_id = @incidentNumber; delete from nf_mod06_data where i_incident_id = @incidentNumber; delete from nf_mod07_chemical_data where i_incident_id = @incidentNumber; delete from nf_mod07_data where i_incident_id = @incidentNumber; delete from nf_mod07_equipment_data where i_incident_id = @incidentNumber; delete from nf_mod07_mobile_data where i_incident_id = @incidentNumber; delete from nf_mod08_data where i_incident_id = @incidentNumber; delete from nf_mod09_data where i_incident_id = @incidentNumber; delete from nf_mod10_data where i_incident_id = @incidentNumber; delete from nf_mod11_agency_data where i_incident_id = @incidentNumber; delete from nf_mod11_arsonist_data where i_incident_id = @incidentNumber; delete from nf_mod11_data where i_incident_id = @incidentNumber; print 'Inside delete block 3 last.\x0A'; -- 06132012 kcp added for new table delete from nf_mod01_basic_otherdata where i_incident_id = @incidentNumber end if;
(13 Jun '12, 12:35)
clayton
Replies hidden
Well, I'm still not certain what (if any) error you are facing - have you tried to debug the stored procedure with Sybase Central? FWIW, 8.0.3.5002 is not only reaaal old but is also the 8.0.3 maintenance release, IIRC - there are lots of bugfixes in "newer" EBFs like 8.0.3.5574 (I guess that's the last one released on Windows, around 2007, methinks). So trying to EBF would make sense...
(13 Jun '12, 12:53)
Volker Barth
I will try the EBF. I have executed through Sybase central with same results. it returns out and never gets to the inside delete block 3 last
(13 Jun '12, 17:19)
clayton
|
|
To repeat what Volker asked, but LOUDER: What is the exact error message you are getting from a DELETE that doesn't find any rows to delete? AFAIK that's SQLCODE = 100 which is just a warning. The following example shows that CALL p ( 999 ) successfully deletes the row from t2 even though there was no matching row in t1. It was run on 8.0.3.5379, although it shouldn't matter WHAT version or build you are running... it shouldn't even matter what SOFTWARE you are using (Oracle etc). CREATE TABLE t1 ( pkey INTEGER NOT NULL PRIMARY KEY, data VARCHAR ( 10 ) NOT NULL ); INSERT t1 VALUES ( 1, 'in t1' ); INSERT t1 VALUES ( 2, 'in t1' ); COMMIT; CREATE TABLE t2 ( pkey INTEGER NOT NULL PRIMARY KEY, data VARCHAR ( 10 ) NOT NULL ); INSERT t2 VALUES ( 1, 'in t2' ); INSERT t2 VALUES ( 2, 'in t2' ); INSERT t2 VALUES ( 999, 'in t2' ); COMMIT; CREATE PROCEDURE p ( IN @pkey INTEGER ) BEGIN DELETE t1 WHERE pkey = @pkey; DELETE t2 WHERE pkey = @pkey; COMMIT; END; SELECT * FROM t1 ORDER BY pkey; SELECT * FROM t2 ORDER BY pkey; pkey,data 1,'in t1' 2,'in t1' pkey,data 1,'in t2' 2,'in t2' 999,'in t2' CALL p ( 1 ); SELECT * FROM t1 ORDER BY pkey; SELECT * FROM t2 ORDER BY pkey; pkey,data 2,'in t1' pkey,data 2,'in t2' 999,'in t2' CALL p ( 999 ); SELECT * FROM t1 ORDER BY pkey; SELECT * FROM t2 ORDER BY pkey; pkey,data 2,'in t1' pkey,data 2,'in t2' Thank you Breck . I will let you know. Some where it is dropping out before the last delete I need near the print 'Inside delete block 3 last.x0A';. The table data is still present. Thank you again for all responses and I will try some more testing on why.
(14 Jun '12, 18:17)
clayton
|
