Jump to content
Joe Sansalone

Interbase Stored Proc, replacing a record

Recommended Posts

Hi,

 

I'm writing a stored procedure to get a list of records AND

replace some of those records with other records IF they have

an "override" - which is indicated by other columns.

 

When I use the stored procedure it correctly REMOVES the record

that has an override BUT does not replace it with its override record.

 

This indicates that I'm not writing the correct code to get new

record and then Suspend to return that record.

 

Below is my stored procedure ....

 

Does anybody see the problem?

 

 

Joe

 

 

 

ALTER PROCEDURE OVERRIDE_LIST 
RETURNS
(
  ID NUMERIC(18, 0),
  DBID INTEGER,
  TABLENAME VARCHAR(15),
  DIMNAME VARCHAR(20),
  DIMVALUE VARCHAR(30),
  RECNO INTEGER,
  DATA BLOB SUB_TYPE 0 SEGMENT SIZE 1024,
  TEXT BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
  REFID NUMERIC(18, 0),
  ISROOT BOOLEAN
)
AS
declare variable I NUMERIC(18, 0);
  declare variable R NUMERIC(18, 0);
begin
  For select ID, DBID, TABLENAME, DIMNAME, DIMVALUE, RECNO, DATA, TEXT, REFID, ISROOT
  From DN
  into :ID, :DBID, :TABLENAME, :DIMNAME, :DIMVALUE, :RECNO, :DATA, :TEXT, :REFID, :ISROOT
  Do
  begin
    i = :ID;
    R = 0;
    execute procedure Override :i Returning_Values :R;

    if (:R is not null) then
    begin
      for select ID, DBID, TABLENAME, DIMNAME, DIMVALUE, RECNO, DATA, TEXT, REFID, ISROOT
      From DN where ID = :R
      into :ID, :DBID, :TABLENAME, :DIMNAME, :DIMVALUE, :RECNO, :DATA, :TEXT, :REFID, :ISROOT
      do
        Suspend;
    end
    else
      Suspend;    
  end
end^

Share this post


Link to post

My mistake.

 

I was using the Stored Proc with a where clause 

which was NOT including the replaced records.

 

Ignore.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×