SF upgrade: Best practice for handling SF model changes in dynamic code with minimal downtime

When upgrading the SF, I am used to making an isolated copy (of the IAM and SF) on which you can perform the model changes and validate that everything is working.
Then during planned downtime, perform the actual upgrade on the SF with the changes that you found when validating the copy of the SF.
It is indeed quite a tedious process, but this way it does not block other team-members from working in the SF.

I wonder if others have a better way...

Did you automate this in some way or did you write an update statement for each control_proc and control_proc_template code field manually?

In the new-version SF, I found control procedures that no longer work. Then, I corrected those in that SF and verified they still worked as intended. Then copy paste it into an Update statement like this, for every fix a new Update statement; yes, manually:

update t1
set t1.control_proc_code = 
N'[Code goes here]'
,t1.update_user = dbo.tsf_user()
,t1.update_date_time = sysdatetime()
from control_proc t1
where t1.model_id = 'MODEL'
  and t1.branch_id = 'BRANCH'
  and t1.control_proc_id = 'CONTROL_PROC_ID'

So the modified control procedure code is captured and then can be executed on the Development SF after it has been upgraded.

Do note that the quotes have to be escpaed with double quotes

It works for both Functionality as well as Dynamic model, as they are both considered Control procedures.

Real example:

print 'meta_add_duration_col';

update t1
set t1.control_proc_code = 
N'insert into #col
(
    tab_id,
    col_id,
    order_no,
    dom_id,
    type_of_col,
    grid_col_width,
    include_in_copy,
    include_in_copy_to_clipboard,
    filter_order_no,
    search_order_no,
    type_of_object,
    form_order_no,
    form_type_of_col,
    grid_order_no,
    card_list_order_no,
    calculated_field_type                                                                             
)                                                                    
select                                                               
    c.tab_id                 as tab_id,
    case
        when c.tab_id in (''job'', ''test_case_run'', ''test_run'', ''test_unit_test'', ''unit_test_result'') 
        then ''duration_ms''      -- Potentially short processes, so duration_ms
        else ''duration_seconds'' -- By default in duration_seconds
    end                      as col_id,
    c.order_no + 1           as order_no,
    case
        when c.tab_id in (''job'', ''test_case_run'', ''test_run'', ''test_unit_test'', ''unit_test_result'') 
        then ''time_in_ms''      -- Potentially short processes, so time_in_ms
        else ''time_in_seconds'' -- By default in time_in_sec
    end                      as dom_id,
    1                        as type_of_col,
    100                      as grid_col_width,
    0                        as include_in_copy,
    0                        as include_in_copy_to_clipboard,
    c.filter_order_no + 1    as filter_order_no,
    c.search_order_no + 1    as search_order_no,
    1                        as type_of_object,
    c.form_order_no + 1      as form_order_no,
    3                        as form_type_of_col,
    c.grid_order_no + 1      as grid_order_no,
    c.card_list_order_no + 1 as card_list_order_no,
    2                        as calculated_field_type
from col c
join tab t
  on t.model_id = c.model_id
 and t.branch_id = c.branch_id
 and t.tab_id = c.tab_id
where c.model_id = @model_id
  and c.branch_id = @branch_id
  -- Use the finish date time to base the select on, used for the order_no fields
  and c.col_id = ''finish_date_time'' 
  and c.dom_id = ''date_time''
  and t.type_of_table = 0 -- Table
  -- Not only the finish date time should be present, also the start_date_time
  and exists (select 1
              from col c2
              where c2.model_id = c.model_id
                and c2.branch_id = c.branch_id
                and c2.tab_id = c.tab_id
                and c2.col_id = ''start_date_time'' 
                and c2.dom_id = ''date_time'')
  -- Only update duration columns created by this control procedure
  and not exists (select 1
                  from col c2
                  where c2.model_id = c.model_id
                    and c2.branch_id = c.branch_id
                    and c2.tab_id = c.tab_id
                    and c2.col_id in (''duration'', ''duration_seconds'', ''duration_ms'')
                    and (c2.generated_by_control_proc_id <> @control_proc_id
                      or c2.generated_by_control_proc_id is null))

insert into #col_query
(
    tab_id,
    col_id,
    rdbms_type,
    calculated_field_query
)
select
    c.tab_id,
    c.col_id,
    0, -- SQL Server
    -- Persisted so it is only recalculated after an update, not after every select (refresh)
    case
        when c.tab_id in (''job'', ''test_case_run'', ''test_run'', ''test_unit_test'', ''unit_test_result'') 
        then ''datediff_big(millisecond, start_date_time, finish_date_time) PERSISTED'' -- Potentially short processes, so time_in_ms
        else ''datediff(second, start_date_time, finish_date_time) PERSISTED''          -- By default in time_in_sec
    end as calculated_field_query
from #col c'
,t1.update_user = dbo.tsf_user()
,t1.update_date_time = sysdatetime()
from control_proc t1
where t1.model_id = 'SQLSERVER_SF'
  and t1.branch_id = 'DEVELOP'
  and t1.control_proc_id = 'meta_add_duration_col'
go

Same goes for validations, updating the validation code in a similar fashion.

Remco Kort did make a Skill for Claude to help with this: 

Edit: After all modifications where done, I did generate Update statements based on the control procedures that had been modified. I used the development_status to determine this. But all code changes were indeed done by my ten fingers and a sprinkle of Github Copliot auto-complete.

I see, writing the update statement doesn’t seem much better than just copy-pasting it from the new-version to the old-version SF though.

But I guess the model changes in 2026.2 were more of an exception so we can live with it being a bit tedious.

As our upgrade is a completely automated process, I didn't want to interrupt that process to copy paste 83 pieces of code in the early morning 😋

After upgrading the SF where the IAM, SF, and Upcycler models reside, the process continues by generating our development models, creating a deployment package for the Upcycler, and creating a Synchronization file for both the SF and IAM in the new version. So, interruppting this process would be more tedious.

And also, with having these upgrade scripts, I could test run the coming upgrade from start to finish to ensure nothing was missed.

I just read your edit, would you mind sharing the meta script to generate the update statements? 

I don't have the actual one anymore as I'm pretty bad at keeping a copy, but this was what it looked like:

declare 
  @model_id model_id         = 'MODEL_ID'
, @branch_id branch_id       = 'BRANCH_ID'
, @developer_id varchar(100) = (select u.usr_id from usr u where rdbms_user_id = dbo.tsf_user())

, @upd_model_id model_id     = 'UPD_MODEL_ID'
, @upd_branch_id branch_id   = 'UPD_BRANCH_ID'

select concat_ws(char(13)+char(10),
'update t1',
'set t1.control_proc_code = ''' + replace(c.control_proc_code, '''', '''''') + '''',
'   ,t1.developer_id =  ''' + @developer_id + '''',
'   ,t1.update_user = dbo.tsf_user() ',
'   ,t1.update_date_time = sysdatetime() ',
'from control_proc t1 ',
'where t1.model_id = ''' + @upd_model_id + '''',
'  and t1.branch_id = ''' + @upd_branch_id + '''',
'  and t1.control_proc_id = ''' + c.control_proc_id + ''''
)
from control_proc c
where c.model_id = @model_id
  and c.branch_id = @branch_id
  and c.assign_type = 2 -- SQL
  and c.development_status = 0 -- In development, so the code was modified
  and c.active = 1 -- Must be active

I am definitely saving this for when we upgrade.
Depending on how many fixes we need to make in the dynamic code this is really great.