Improve workflow for new control procedures and use program object signature as template comment

Hi Dave,

I would like to point out 2 things.

1. In the 2023.2 we improved the way of editing program object code (see URL for more info). The advantage of using this feature is that you already have access to all parameters, and that the SF ensures that the changes you make are saved in the appropriate templates.
2. In the upcoming 2024.2, we will add a model enrichment (see URL for more info) that enables you to create application logic based on natural language. You can choose if you want to limit this to 1 table or not. It will add a control procedure and template, assign it to the relevant program object(s), and generate the program object(s) for you.
    

   

Would the combination of these 2 features be sufficient in your situation?

Jeroen

@Jeroen van den Belt 

to be clear, with parameters from the signature I meant the parameters of the prog object.
 

create procedure def_project
(
   @default_mode       tinyint,             /* 0 = add, 1 = update */
   @import_mode        tinyint,             /* 0 = regular, 1 = import */
   @cursor_from_col_id varchar(255),        /* trigger column */
   @cursor_to_col_id   varchar(255) output, /* focus column */

   /* column values */
   @project_id         project_id output,
   @project_desc       description output,
   @finished           no_yes output,
   @finished_date      date output,
   @insert_user        user_name output,
   @insert_date_time   date_time output,
   @update_user        user_name output,
   @update_date_time   date_time output
)

Hi Dave,

That's how I interpreted it 🙂. For clarity for other users, I changed point 4 in your original text a bit.

What I meant was, if you already have the parameters in the program object code, and you are also able to directly edit the program object code, wouldn't that suffice in your situation? You can easily copy the desired parameters to the code-part below, write your code and then save it. The SF does the rest for you.

@Jeroen van den Belt 

To clarify:

My idea is particular of interest in the process of creating a new template and assigning it. (or vice versa)

There are two ways to accomplish that

You follow the order of tabs/screens given in the software factory:

1. Screen “Control procedures”: Create a new control procedure
2. Screen “Templates”: Create a new control procedure template. You can’t begin coding yet, for you don’t know the parameters from the signature of the program object yet. You place some dummy code (like a comment) to be able to save the control procedure template, because it’s mandatory.
3. Screen “Assigning”: Assign the template
4. Screen “Deploy”: Generate
5. Copy the signature parameters of the program object.
6. Screen “Templates”: Open the code and paste the signature parameters
7. Happy coding!

You don’t follow the order of tabs/screens:

1. Screen “Control procedures”: Create a new control procedure
2. Screen “Assigning”: Assign the dummy template. A new control proc template will be generated for you.
3. Screen “Deploy”: Generate
4. Copy the signature parameters of the program object.
5. Screen “Templates”: Open the code and paste the signature parameters
6. Happy coding!

I suggest a flow a long the lines of this:

1. Screen “Control procedures” create a new cp. 
   1. Offer the option to assign it to a program object
   2. Automatically generate
      1. the program object (for the signature)
      2. the template
         1. put the signature parameters in the template (in comments if you must)
2. Happy coding

We will go from 6 steps to only 2 steps/clicks/screens.

Alternatively, since you can also code in the program object it self, maybe that should be the second tab page, and leave templates as an advanced option. But as it is now, the order of tabs suggests another workflow and doesn’t reduce the amount of steps.

Although we also use the copying of the signature into remarks of the control procedure, we were thinking about a slightly different solution. There is a lot of white space on the right side of the editors frame. Wouldn’t it be possible to show the signature of the controle procedure there instead of pasting it into the code. This would then always show the actual signature and there would be no need to copy it at all (unless you want to ofcourse).

@Robert Wijn 2 also a good idea. However, you won’t see the signature then when you open the code file in an SQL editor such as SSMS. I like to have them available in the code it self.

And if you put a declare before it, you can actually use it in the code to let intellisense recognize the parameters.

declare
   @default_mode       tinyint,      -- 0 = add, 1 = update
   @import_mode        tinyint,      -- 0 = regular, 1 = import row, 3 = import
   @cursor_from_col_id varchar(255), -- column that triggered the default
   @cursor_to_col_id   varchar(255), --output, -- cursor in column after leaving the default
   @auto_commit        bit,          --output, -- 1 = auto commit

   @model_id           "model_id",    --output,
   @branch_id          "branch_id",   --output,
   @sync_target_iam_id "id",          --output,
   @host               "server_name", --output,
   @db_name            "db_name"      --output

Not sure how this would work if you have multiple assignments in place. My idea was to just show the available parameters (per assigned object on the right) ill leave it up to the development team what the best solution would be :