Skip to main content
Solved

Add input parameters to functionality SQL procedure

  • August 13, 2025
  • 6 replies
  • 54 views
C
Rookie
Forum|alt.badge.img

Hi, we have a sql procedure for data migration of our old system.

The procedure is generating all merge statements for our new TW tables and column with the ones of our old database.

We already have merge statements for all data + statements for a small dataset (using a extra table)

But now we want to add to merge 1 record, so I need an input parameter in the procedure: the key value of the record.

We have an functionality with code and different templates.

 

For all these procedures there is also generated a subroutine.

Is it possible to generate process parameters for some templates using dynamic code in the functionality?

create or alter procedure "myProc"(   -- Process variables    @myTableKey int )

and also generated the parameters in the generated subroutines?

Greets

Chris

Best answer by Mark Jongeling

Hi,

In some way the "data_as400_naar_tw” procedures are generated. In that same procedure, you can add an insert into the subroutine_parmtr tabel to add an input parameter to all procedures (or a subset if desired).

In case you use the Staged strategy for the Dynamic model procedure, you can use this example:

insert into #subroutine_parmtr
(
    subroutine_id,
    subroutine_parmtr_id,
    dom_id,
    order_no,
    input_parmtr,
    output_parmtr
)
select 
    'data_as400_naar_tw' + [Something]  as subroutine_id,
    'key_value_parmtr_id'               as subroutine_parmtr_id,
    [Place here the domain you need]    as dom_id,
    10                                  as order_no,
    1                                   as input_parmtr,
    0                                   as output_parmtr
from [Where you can find the desired data]

In this template, you will need to change a couple things to make it work for your situation. Namely:

  • Subroutine_id = Procedure name, is determined in a way I do not know right now
  • subroutine_parmtr_id, you can change the name to something else
  • dom_id, you can enter the desired domain here, for instance an integer domain to correspond to the record in the table
  • From clause, currently I don't know for which tables this is desired

You can also add multiple parameters this way, or even have some procedures with more or less parameters than others. You could, for example, base it on the Primary key columns of the tables that are desired.

Hope this helps!

    This topic has been closed for replies.

    6 replies

    Mark Jongeling
    Administrator
    Forum|alt.badge.img+23

    Hi,

    I suppose you have a Dynamic model procedure that generates your Subroutines at the moment. That procedure should now generate a subroutine parameter that you will use to specify the key value of the record.

    In your control procedure, which I suppose is also uses SQL assignments, you will need to ensure the template uses that new parameter. It maps to the unique column of the table to select the intended row, or, if null, all records.

    ...
    and (@key_value = t1.col_id or @key_value is null)

     This is something I would expect the template to now have. Does this help enough to achieve the goal?

    If not, some more information about the current set up will help determine the right course to take. Such as, how the procedures are now generated, the control procedure and its template, how assignments are done.

     

    "Wake me up before you low-code"
    C
    Rookie
    Forum|alt.badge.img
    • c.houbenRookie
    • Author
    • Rookie
    • August 14, 2025

    Hi Mark,

    We have a control procedure with sets up the parameters for the templates:

     

    Then we have a template that generate the merge statement for all our tables for data migration:
     

    This generates our sql data migration statements:
    Now I want to add some more program objects so I can merge the data with input parameters (key of the table). I don’t see where I can add the input parameters for the procedure. I want to generate these parameters thru the code of the functionality, because for all procedure, are the key of the table.

    Same for the dynamic model code, where or how can you add input parameters for the procedure?
     

     

    Mark Jongeling
    Administrator
    Forum|alt.badge.img+23
    • Mark Jongeling
    • Administrator
    • Answer
    • August 14, 2025

    Hi,

    In some way the "data_as400_naar_tw” procedures are generated. In that same procedure, you can add an insert into the subroutine_parmtr tabel to add an input parameter to all procedures (or a subset if desired).

    In case you use the Staged strategy for the Dynamic model procedure, you can use this example:

    insert into #subroutine_parmtr
    (
        subroutine_id,
        subroutine_parmtr_id,
        dom_id,
        order_no,
        input_parmtr,
        output_parmtr
    )
    select 
        'data_as400_naar_tw' + [Something]  as subroutine_id,
        'key_value_parmtr_id'               as subroutine_parmtr_id,
        [Place here the domain you need]    as dom_id,
        10                                  as order_no,
        1                                   as input_parmtr,
        0                                   as output_parmtr
    from [Where you can find the desired data]

    In this template, you will need to change a couple things to make it work for your situation. Namely:

    • Subroutine_id = Procedure name, is determined in a way I do not know right now
    • subroutine_parmtr_id, you can change the name to something else
    • dom_id, you can enter the desired domain here, for instance an integer domain to correspond to the record in the table
    • From clause, currently I don't know for which tables this is desired

    You can also add multiple parameters this way, or even have some procedures with more or less parameters than others. You could, for example, base it on the Primary key columns of the tables that are desired.

    Hope this helps!

    "Wake me up before you low-code"
    C
    Rookie
    Forum|alt.badge.img
    • c.houbenRookie
    • Author
    • Rookie
    • August 14, 2025

    Hi Mark,

    “In some way the "data_as400_naar_tw” procedures are generated.” 

    These procedures are generated with code in the functionality using #prog_object_item and #prog_object_item_parmtr.

     

    When I use #prog_object_item_parmtr in the dynamic model code, I get a reference error ("ref_subroutine_subroutine_parmtr"). So this means if I correct the reference, I can use this in dynamic model.

    But when I use #prog_object_item_parmtr in the fuctionality code, I get: Invalid object name '#subroutine_parmtr'.

    So this means I can’t use this in functionalities or am I doing something wrong?

     

    Mark Jongeling
    Administrator
    Forum|alt.badge.img+23

    Your procedures are generated, with that I mean the Subroutine records in the model, not the actual code itself. In your Control procedure you do use the PROCEDURES Code group, meaning you are linking to existing Subroutines (of the type Procedure).

    Can you see by what Dynamic model procedure these are generated? In the Subroutine screen, select one of those and inspect the Trace info tab of the Form. There is should mention the control procedure they are generated by.

    Or are you perhaps generating those subroutines from within the control procedure code of "proc_data_as400_naar_tw”? ← This is considered a bad practice.

    Depending on where the subroutine records are generated from, the subroutine_parmtr insert should also take place in there, after inserting the subroutine records.

     

    "Wake me up before you low-code"
    C
    Rookie
    Forum|alt.badge.img
    • c.houbenRookie
    • Author
    • Rookie
    • August 14, 2025

    Mark, 

    You are correct ! I didn’t know that we had dynamic model code that generated the subroutines.
    I assumed that they were generated by the functionality.

    Thanks for the help and the missing table #subroutine_parmt.

    Mark Jongeling
    Terms & ConditionsCookie settingsAccessibility statement