Passing parameter value to SQL job

Question

Passing parameter value to SQL job

Husain_Kachwala

Ten Centuries

Points: 1054
More actions
April 10, 2009 at 1:25 am

#202913

Hi All,
I have a situation. I have a job in sql server 2005 which executes a stored procedure.The problem is that this stored procedure accepts two parameters @StartDate datetime,
@EndDate datetime.
How do I pass these parameters to the job?

Viewing 15 posts - 1 through 15 (of 17 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You do it the exact same way that you pass a parameter to a stored procedure from a batch. The fact that you invoke the stored procedure from a job has no impact on the way that you work with parameters.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

raj acharya SSCrazy Points: 2805 More actions · Answer 2

if you want to pass parameter into sql job ...first of job means...you must put facility that when sp executes it automatically take parameter from somewhere ...now another thing ....i m giving you one idea .....put startdate and enddate in one table as per the currentdate....it will inserted into that and when sp executes it will take latest parameter from that table.....

Raj Acharya

Husain_Kachwala Ten Centuries Points: 1054 More actions · Answer 3

Thanks Adi & Raj for the reply.

The situation is that the user will only execute the job. I wanted to know if there is some mechanism in sql server 2005 wherein once the job is invoked, the user is asked to enter parameter values for the same.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

I’m not sure that you have to do it as a job. A job is a scheduled task. It is executed according to the time and date. If a user has to execute it, then maybe it should be just a stored procedure that gets the parameter from the user.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Husain_Kachwala Ten Centuries Points: 1054 More actions · Answer 5

Yeah you are right but the situation is such that the business users wont bother executing the stored procedure.

Changes at table level is not possible since the system would be in production soon.

krayknot SSCertifiable Points: 7998 More actions · Answer 6

Husain_Kachwala (4/10/2009)
Hi All,
I have a situation. I have a job in sql server 2005 which executes a stored procedure.The problem is that this stored procedure accepts two parameters @StartDate datetime,
@EndDate datetime.
How do I pass these parameters to the job?

Use the batch file or you can also use temp table

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Husain_Kachwala Ten Centuries Points: 1054 More actions · Answer 7

Husain_Kachwala

Ten Centuries

Points: 1054

April 10, 2009 at 5:21 am

#975658

Could you please elaborate on the use of both...

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 8

I’m sorry but I have a feeling that we don’t understand each other (maybe because English is the second language for both of us). Can you try and explain the situation? From what I understand you need to run a stored procedure. The stored procedure should get parameters. What I don’t understand is if it should run when a user wants it to run or based on date and time. Can you pleas explain what you need?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

raj acharya SSCrazy Points: 2805 More actions · Answer 9

hi i think your requirement is like...for example you have schedule job for procedure and you want that each time when job executes it will ask users for parameter .....is this right then

this kind of thing can't possible when you automatically schedule job.....in job sp will never ask for parameter you should do arrangement like that it can get input parameter when it runs.....

can u post me your requirement in detail.....but as per i told you there is no mechanism like during when job executes it will ask for parameter ....it can happen only through application side ...or you can generate report for that...

Raj Acharya

Husain_Kachwala Ten Centuries Points: 1054 More actions · Answer 10

Thanks Adi & Raj for your responses.

Raj has been on the point. It seems I'll have to change my approach to acheive the same.

Thanks All for your valuable responses.

Husain_Kachwala Ten Centuries Points: 1054 More actions · Answer 11

Raj has got the right point. It seems I'll have to change my approach.

Thanks Guyzzz..

raj acharya SSCrazy Points: 2805 More actions · Answer 12

you are welcome any time....we are here to help each other's difficulty

Raj Acharya

Husain_Kachwala Ten Centuries Points: 1054 More actions · Answer 13

Thanks buddy...Will bug you if any probs...Can I have your Email ID?

raj acharya SSCrazy Points: 2805 More actions · Answer 14

my mail id is :- rajacharyaengg@gmail.com

you can mail me....but also put your questions here....bec more hands better then one......

Raj Acharya