I am working with D6 and Sybase ASA V5. I wrote a procedure in ASA whose
role is to perform the equivalent of a partial cascade delete; it does
NOT return anything.
It compiled and is stored in the DB(See code below). I then created a
stored procedure (TStoredProc)in my DataModule in D6.

Stored Procedure compiled and stored in Sybase ASA V5 Database:

Create procedure "dba".sp_DeleteScanItemsSet(in iClipDate varchar,in
iSourceID integer,in iClipPage varchar,in iKeywordID integer,in
iTitleWords varchar)
begin
   -- Procedure-wide variables
   declare CurrLineID integer;
   declare err_notfound exception for sqlstate value '02000';
   -- Cursor for reading ScanList Rows
   declare cur_ScanSet dynamic scroll cursor for
   Select LineID from IMDBA.ScanList as SL
       where
           SL.ClipDate=iClipDate
       and SL.SourceID=iSourceID
       and SL.ClipPage=iClipPage
       and SL.TitleWords=iTitleWords;
   open cur_ScanSet;
   GetScanListLoop: loop
     -- Start loop to read all ScanList rows and process each in turn
     fetch next cur_ScanSet into CurrLineID;
     if sqlstate=err_notfound then
       leave GetScanListLoop
     end if
     ;
     -- Delete from table ClipXCustomers
     delete from IMDBA.ClipXCustomers where LineID=CurrLineID;
     -- Delete from table ScanListFiles
     delete from IMDBA.ScanListFiles where LineID=CurrLineID
   end loop GetScanListLoop
   ;
   -- Finally, close cursor
   close cur_ScanSet
end

I didn't change the Paramaters of the D6 TStoredProc as they were
already set when I clicked on the Params property of the TStoredProc;
Also, the documentation suggests not to change them, as their data type
is set by the DB server.

Here are the parameters (Matching the DB stored procedure parameters).

iClipDate   (DataType: ftMemo) <- not sure why, as it's a Date in the DB
iSourceID   (DataType: ftInteger)
iClipPage   (DataType: ftString)
iKeywordID  (DataType: ftInteger)
iTitleWords (DataType: ftString)

I tried to change iClipDate above to ftString (D6 passes it to the
procedure as a string)- with no changes in behaviour).

In the Application, I attempt to use the stored procedure as follows:

First, I load the parameters, for example the iClipDate param:

  dmIMC.spDeleteFromScanList.ParamByName('iClipDate').AsString :=
           fmScanListAdd.DBGrid1.SelectedField.AsString;

I debugged the parameters just before calling the stored procedure, and
they all contain the expected data.

Second, I call the procedure with:

  dmIMC.spDeleteFromScanList.Prepare;
  try
    dmIMC.spDeleteFromScanList.ExecProc; // Exec the procedure in the DB
  except
  dmIMC.spDeleteFromScanList.Close; // Close this procedure
  end;

The Prepare statement executes OK, but the ExecProc fails with the
message (I also tried to use .Open instead of .ExecProc, same results):

<<Project IMScan.exe raised exception class EVariantError with message
'Invalid Variant Type Conversion'>>.

Any idea what the problem might be? If I don't find a fix, I'll have to
implement the stored procedure logic in the D6 application (a pain...).

Any help will be appreciated!

Regards,

Jean-Rene Nicolet