sp_add_jobstep

• David-250683

  Hall of Fame

  Points: 3452

  More actions

  July 31, 2007 at 10:47 am

  #118029

  Can a variable be passed to the @command line?  If so, how?

  Here's a portion of what I have:

  DECLARE @UserToFind varchar(30),

    @NewUserName varchar(30),

    @JobName varchar(30)

  SELECT @UserToFind = 'bonnien',

    @NewUserName = 'bonnien_trm',

    @JobName = 'TestJob' + CONVERT(varchar(30), GETDATE(), 120)

  EXEC msdb.dbo.sp_add_job

   @job_name = @JobName,

      @enabled = 1,

      @description = 'TestJob',

  EXEC msdb.dbo.sp_add_jobstep

   @job_name = @JobName,

      @step_name = 'TestJobStep',

      @subsystem = 'TSQL',

      @command = 'EXEC TEST_abc_UserNameUpdate ''bonnien'', ''NewUser''',    -- THIS WORKS WITHOUT THE VARIABLES

   --@command = 'EXEC TEST_abc_UserNameUpdate '@UserToFind, @NewUserName'  How can I pass these variables?

  EXEC msdb.dbo.sp_add_jobserver

   @job_name = @JobName,

   @server_name = 'gptest'

  EXEC msdb.dbo.sp_add_jobschedule

   @job_name = @JobName,

      @name = 'TestJobSchedule',

      @freq_type = 1,

      @active_start_time = 093900 --HHMMSS

• David-250683

  Hall of Fame

  Points: 3452

  More actions

  July 31, 2007 at 11:16 am

  #723667

  I just figured it out.

  I added a variable: @sql varchar(100)

  Populated it with the value I wanted: @sql = 'EXEC TEST_abc_UserNameUpdate ' + @UserToFind + ', ' + @NewUserName .

  Then used @command = @sql, in sp_add_jobstep.