Passing Parameters To a JOB?

• bbron

  SSC Eights!

  Points: 936

  More actions

  February 18, 2004 at 9:11 pm

  #160752

  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?

   

• TONYMARKS

  SSC Journeyman

  Points: 77

  More actions

  February 20, 2004 at 6:02 pm

  #495020

  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

• Calvin Lawson

  SSChampion

  Points: 11030

  More actions

  February 20, 2004 at 6:54 pm

  #495027

  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