Passing Parameters To a JOB?

  • Hi,

    There's a procedure that requires a parameter (@Param1).  That procedure takes quite some time to be executed.  And we want a user to be able to execute it via ASP.  So we created a job to execute that job... and another to start the job.  Can I pass a parameter to the job?

     

  • I don't think that jobs (SQL Server Agent jobs, i presume) take parms. (If I'm wrong, someone slap me silly and let me know...)

    Here's how a user (via your app and a web connection) could change parms and start a job (named "my_job") to run a procedure (name "my_sp")...

    1. The user enters the parms then clicks OK .
    2. The web task stores the user's parms on a row in a table (let's call the table "parm_tbl").
    3. Assuming your job name is "my_job", and that it runs your stored procedure named "my_sp", the web task then executes the TSQL :

                         

                          exec sp_start_job 'my_job'

    4. The first thing "my_sp" does is query "parm_tbl" for values to store in TSQL variables.  (It's probably a good practice to remove the row, or somehow mark the row as "processed".)
    5. "my_sp" continues execution using values from "parm_tbl". "my_sp" could also call any number of other stored procs.

    This technique starts the (long running) job asynchronously, and returns control to your application and user while the job is running. The user could logout or whatever, but the job is still running.

    There are any number of variations to this.

    Hope this helps ...

     


    TONYMARKS

  • Excellent Idea, Tony.  I've starting using parameterless procs for a number of reason's, the main being performance.  In fact, I'm working on a generic Parameter table to deal with this.  Something like:

     

    CREATE TABLE [Proc] (

     [ProcID] [int] IDENTITY (1, 1) NOT NULL ,

     [Name] [sysname] NOT NULL ,

     [Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

      PRIMARY KEY  CLUSTERED

     (

      [ProcID]

    &nbsp  ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    CREATE TABLE [ProcParam] (

     [ProcParamID] [int] IDENTITY (1, 1) NOT NULL ,

     [ClientID] [int] NOT NULL ,

     [ProcID] [int] NOT NULL ,

     [OrdNum] [int] NOT NULL CONSTRAINT [DF__ProcParam__OrdNu__5A054B78] DEFAULT (0),

     [Name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

      PRIMARY KEY  CLUSTERED

     (

      [ClientID],

      [ProcID],

      [OrdNum]

    &nbsp  ON [PRIMARY] ,

      FOREIGN KEY

     (

      [ClientID]

    &nbsp REFERENCES [Client] (

      [ClientID]

    &nbsp,

      FOREIGN KEY

     (

      [ProcID]

    &nbsp REFERENCES [Proc] (

      [ProcID]

    &nbsp

    ) ON [PRIMARY]

    GO

    I haven't had any time to work on this lately, though, except:

    create    function getParameter (@ParamterID int)

    returns varchar(8000)

    as

    begin

    declare @string varchar(8000)

    select @string = isnull(@string + ', ', '') + isnull(Name + ' = ', '') + Value

    from Parameter p (nolock)

    where ParameterID = @ParamterID

    order by OrdNum

    select @string = isnull(@string, '')

    return @string

    end

    This would return your parameter string as a csv.

    Signature is NULL

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

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