Dynamic Column update.

Question

Dynamic Column update.

SSCrazy

Points: 2756

April 2, 2020 at 11:30 am

I would like to update TASK_NXT_RUN_DT column based on the script in column 2 dynamically. I have tried using while loop and dynamic sql but no luck. Any help on this much appreciated

create table #CFG_PROCESS_EVALUATE_TASK_MSTR (
taskid int,
TASK_SCHED_SQL_SCRIPT varchar(500),
TASK_LST_RUN_DT datetime,
TASK_NXT_RUN_DT datetime
)
insert into #CFG_PROCESS_EVALUATE_TASK_MSTR
select 1,'dateadd(dd,1,TASK_NXT_RUN_DT)','2020-04-02 03:17:40.043','2020-04-02 03:17:24.353' union all
select 2,'dateadd(dd,10,TASK_NXT_RUN_DT)','2020-04-02 03:17:40.043','2020-04-02 03:17:24.353'

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

koti.raavi wrote:

I would like to update TASK_NXT_RUN_DT column based on the script in column 2 dynamically. I have tried using while loop and dynamic sql but no luck. Any help on this much appreciated
create table #CFG_PROCESS_EVALUATE_TASK_MSTR (
taskid int,
TASK_SCHED_SQL_SCRIPT varchar(500),
TASK_LST_RUN_DT datetime,
TASK_NXT_RUN_DT datetime
)
insert into #CFG_PROCESS_EVALUATE_TASK_MSTR
select 1,'dateadd(dd,1,TASK_NXT_RUN_DT)','2020-04-02 03:17:40.043','2020-04-02 03:17:24.353' union all
select 2,'dateadd(dd,10,TASK_NXT_RUN_DT)','2020-04-02 03:17:40.043','2020-04-02 03:17:24.353'

Have you considered using a different model? For example, create an INT column called 'DaysToAdd' (set it to 1, 10 in your example above). The UPDATE query then becomes trivial.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.