none
Pass Parameter to Job which calls a SP with the Parameter provided?

    Question

  • ok, I have a problem that is getting the better of me.

    I have an SP called sp_Rep_QTR which expects the following parameters @stardate,@Endate.

    Now what I want to do is the following,

    Create a Job which will EXEC the sp_Rep_QTR at a time(@active_start_time ) provided and pass the @stardate,@Endate parameters to the SP .

    The @stardate,@Endate parameters will be passed to a SP which will create the Job - These are passed from a web page.

    I'm completely in over my head on this one so any help would be great.


    Thanks..



    • Edited by Ray Mantle Saturday, February 15, 2014 5:55 PM
    Saturday, February 15, 2014 5:47 PM

Answers

  • The @stardate,@Endate parameters will be passed to a SP which will create the Job - These are passed from a web page.

    Although you can't pass parameters to the job in the classical sense, you can pass the needed values via a table.  The job step can read the needed parameter values from the table and pass as parameters to the stored procedure.

    Also, do not use "sp_" as a stored procedure name prefix.  That prefix is reserved for system stored procedures.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Ray Mantle Sunday, February 16, 2014 10:30 AM
    Saturday, February 15, 2014 6:58 PM

All replies

  • You can not pass parameters to JOB. 

    What would be your startdate and enddate? If these are constants, then you can directly use those values when you call the procedure from the JOB. IF not, you need to wrap your procedure in another procedure where you need to find the startdate and end date and pass as parameters. This way, you can call your WRAPPER procedure from JOB. Hope, this would help you.

    Saturday, February 15, 2014 6:00 PM
  • The @stardate,@Endate parameters will be passed to a SP which will create the Job - These are passed from a web page.

    Although you can't pass parameters to the job in the classical sense, you can pass the needed values via a table.  The job step can read the needed parameter values from the table and pass as parameters to the stored procedure.

    Also, do not use "sp_" as a stored procedure name prefix.  That prefix is reserved for system stored procedures.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Ray Mantle Sunday, February 16, 2014 10:30 AM
    Saturday, February 15, 2014 6:58 PM
  • I assume the point is that the user should be permitted to start an asynchronous process from a web page. Rather than starting a job, I would look into using Service Broker. The webpage would post a message on a Service Broker queue, and that would fire an activation procedure on the other end. Passing parameters is a trivial matter in this case, as you include them in the message body.

    You will have to invest some time to learn Service Broker, but there is less red tape associated with Service Broker than with agent jobs. Particularly, Service Broker is (in this case) entirely in the database with no dependency on the server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 15, 2014 7:13 PM
  • One way of hacking this problem is by dynamically updating the job step which executes the stored procedure to include the parameters the user provided.  You can do so by using msdb.dbo.sp_update_jobstep.

    So for example, you could create a job named "Test job" and in that job the first step would be to execute your stored procedure.  You would then use the following code to update that step at run time:

    execute	msdb.dbo.sp_update_jobstep
    		@job_name = N'Test job',
    		@step_id = 1,
    		@command = 'execute my_proc @my_variable = ''my_value'''
    You'd then use sp_start_job like so:
    execute msdb.dbo.sp_start_job @job_name = 'Test job'
    Your job would then execute using the value provided at run time for the @my_variable parameter.
    Sunday, February 16, 2014 3:23 AM
  • Thanks Dan,

    I found this option the best way forward. Now instead of having lots of jobs running reports, i have one job which scans the tblRequestedReports table and exec a report based on the request.

    Thanks.

    Sunday, February 16, 2014 10:33 AM