Add job using tsql related

Posted on

Question :

When using sp_add_jobstep, are there any limits on @command when I choose @subsystem to ‘TSQL’, say, maybe I can only set @command to some simple, single tsql like:

'select * from o2o.dbo.sth'

Or could it be complicated like this?:

use hxo2o 
go
begin
  declare @last_sync_date_updater nvarchar(2000)
  declare @tb_name nvarchar(200)
  declare tb_name_cur cursor for
    select table_name from dbo.o2o_tables

  open tb_name_cur
  fetch next from tb_name_cur into @tb_name

  while @@fetch_status = 0
  begin
    set @last_sync_date_updater =
        'update dbo.o2o_tables set table_last_sync_date = (select '
        + case
        when @tb_name = 'trace_user_actions' then 'max(operation_date_time)'
        else ' dbo.InlineMax(max(create_time), max(update_time)) ' end
        + 'from dbo.' + @tb_name + ')'

    exec (@last_sync_date_updater)

    fetch next from tb_name_cur into
    @tb_name
  end
close tb_name_cur
deallocate tb_name_cur
end

If not, should I use another @subsystem instead? Or some other methods? I tried to google and stack for the issue but labor in vain.

Answer :

It can be as complicated as you want, no issues with that. My own recommendation, for better manageability, if the tsql you are planning to use on the job step is too long, better create a stored procedure. This way you just call the sp from the job step and you do all your modifications there. And on the job itself just take care of the scheduling.

Leave a Reply

Your email address will not be published.