Topic
  • No replies
1V8Q_keith_hammel
1V8Q_keith_hammel
1 Post

Pinned topic function to update data and return new value

‏2013-09-20T16:37:19Z |

I would like to create a function to take input, read value, update the value, and return the value.

and now use this function in bulk update of records.

create or replace function kh_get_next_seq (sequence_name Varchar(32))
    returns int
    language sql
    Modifies sql data
    No external action
    Begin not atomic
        Declare next_record int;
        Update kh_sequence set nextvalue = nextvalue + 1 where sequencename = sequence_name;
        set next_record = (select nextvalue from kh_sequence where sequencename = sequence_name);
        Return (select nextvalue from kh_sequence where sequencename = sequence_name);
    End %

insert into kh_assigned_cat (ASSIGNEDCATEGORYID, LICCATEGORYID, LICENSENUMBER)
Values (
select KH_GET_NEXT_SEQ('ASSIGNEDCATEGORY'),LICCATEGORYID, LICENSENUMBER from kh_assigned_cat where liccategoryid = 5)%
 

When I run as above it get DB2 Database Error: ERROR [42613] [IBM][DB2/NT] SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL DATA" clause are present. LINE NUMBER=18. SQLSTATE=42613

When I put in a table I get error abut function context error, I for get exactly the error.

usind DB2 9.7  on Windows server.