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.