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 ON EXCEPTION RESUME but I get syntax errors. 8.0.3 5002 ASA. Here is what we have as original

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?

asked 13 Jun '12, 11:16

clayton's gravatar image

clayton
31124
accept rate: 0%

edited 13 Jun '12, 11:27

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109


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.

permanent link

answered 13 Jun '12, 11:31

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

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?

permanent link

answered 13 Jun '12, 11:35

Volker%20Barth's gravatar image

Volker Barth
30.6k306456662
accept rate: 32%

got the syntax to work with your suggestions but still no working as I want.
The delete may be a row that does not exist in a table eg. table1. You are correct not an error since it does not exist but the procedure falls out and returns without going to the next, next and so on. Any many rows may not be present like table2-table(n) . Many physical records may make up a logical record for a type of incident we need. We need to delete all ones that are present in the procedure but if some are not in the DB it must continue to finish the ones that are.

(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'
permanent link

answered 13 Jun '12, 17:40

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
accept rate: 20%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106

question asked: 13 Jun '12, 11:16

question was seen: 1,278 times

last updated: 15 Jun '12, 03:16