Stored procedure where the input and output is a list of integers

[former_member287920]

I have a scenario where I'd like to create a stored procedure that takes a list of integer ids as input. The procedure needs to update two tables with each id and if either update fails, then the update should fail completely for that id. I'd like to be able to return a list of all the ids that failed when the procedure is done. I know I can do transactions in a procedure to guarantee all-or-nothing success but I'm not sure if I can pass and return lists to a procedure. I looked around online but didn't find anything helpful

[lbreddemann · Accepted solution]

Hi Steve,

thanks for posting this here, so that others can contribute and use this question.

Based on your description you have several requirements:

• process lists of record IDs in a procedure
• keep a list of IDs that haven't been processed
• have transactional consistency up the procedure call

One approach for that could look like the following:

select current_timestamp, * from m_database;
/*
CURRENT_TIMESTAMP           SYSTEM_ID   DATABASE_NAME   HOST        START_TIME                  VERSION                 USAGE 
21/06/2018 12:18:19.605 AM  S12         S12             skullbox    20/06/2018 10:04:22.642 PM  1.00.122.16.1520578817  CUSTOM
*/ 
create column table a (id int, name nvarchar(20));
create column table b (id int, name nvarchar(20));


insert into a values (1, NULL);  -- present in both tables
insert into b values (1, NULL);
insert into a values (2, NULL); -- only present in A
insert into b values (3, NULL); -- only present in B
insert into a values (5, NULL); -- id 4 not existent at all
insert into b values (5, NULL); -- id 5 present in both tables

select  a.id as A_ID, a.name as A_NAME
     ,  b.id as B_ID, b.name as B_NAME
from a full outer join b 
on a.id = b.id
order by coalesce(a.id, b.id);

/*
A_ID    A_NAME  B_ID    B_NAME
1       ?       1       ?     
2       ?       ?       ?     
?       ?       3       ?     
5       ?       5       ?     
*/
create global temporary table GT_FAILED_IDS (id int, failed_update nvarchar(10));

drop procedure upd_data;
create  procedure UPD_DATA (in upd_ids TABLE (id int),
                                      out failed_ids TABLE (id int
                                                          , failed_update nvarchar(10))
                                                     )
as 
begin
declare cursor batchlist for  select id from :upd_ids;
   for cur_row as batchlist do 
        update a set name = 'PROCESSED_A' where id = cur_row.id;
        if ::ROWCOUNT = 0 then
            insert into  GT_FAILED_IDS 
                VALUES (cur_row.id,  'A');
        end if;
        update b set name = 'PROCESSED_B' where id = cur_row.id;
        if ::ROWCOUNT = 0 then
            insert into  GT_FAILED_IDS 
                VALUES (cur_row.id,  'B');
        end if;
   end for; 
   failed_ids = select id
                     , string_agg(failed_update, ', ') as failed_update
                from GT_FAILED_IDS
                group by id;
   commit;
end;

-- Test the procedure 
do begin
    -- select IDs 1, 2, 3, 4, 5
    in_ids = select 1 as ID from dummy
             union all 
             select 2 as ID from dummy
             union all 
             select 3 as ID from dummy
             union all
             select 4 as ID from dummy
             union all
             select 5 as ID from dummy;
    call upd_data (:in_ids, :out_ids);
    select 'OUT:', * from :out_ids;                         
end;
/*
'OUT:'  ID  FAILED_UPDATE
OUT:    2   B           
OUT:    3   A           
OUT:    4   A, B         
*/

select  a.id as A_ID, a.name as A_NAME
     ,  b.id as B_ID, b.name as B_NAME
from a full outer join b 
on a.id = b.id
order by coalesce(a.id, b.id);

/*
A_ID    A_NAME      B_ID    B_NAME     
1       PROCESSED_A 1       PROCESSED_B
2       PROCESSED_A ?       ?         
?       ?           3       PROCESSED_B
5       PROCESSED_A 5       PROCESSED_B
*/

Using the global temporary table to keep track of the failed IDs is not a hard requirement here. Also, I only focus on the condition that no record to update has been found. You may have more/different failure conditions to check for.

The main part to this approach is of course: using the table typed parameters to handle lists and the ::ROWCOUNT session variable.

Hope that points you into the right direction.

Cheers,

Lars

[former_member287920 · Accepted solution]

Hi Lars,

Thanks for the very detailed answer! The use of table variables is new to me but I can see how that would work. One follow-up question. I might be interpreting this wrong (or most likely I didn't describe the requirement well) but if an insert to A fails, I see that it adds the failed id to the list of failed ids as requested but then it looks like it still attempts the insert to B. What I need to happen is if ANY insert fails to either table, then it's as if no insert was attempted and the id is added to the failed id table variable. This probably requires a transaction inside the procedure yes?

Regards,

Steve

[lbreddemann · Accepted solution]

Yes, correct. If you want to ensure that all updates or none concerning one ID have been performed, then yes, you would use a transaction inside the procedure.

That's no problem however, you can simply issue COMMIT or ROLLBACK inside the procedure. Just be aware that this is not a sub-transaction; so you will want to make sure that no other changes are uncommitted in your session before calling the procedure.