Any way to Pass Parameters to Jobs?

Question

Any way to Pass Parameters to Jobs?

Pulivarthi Sasidhar

SSCertifiable

Points: 6706
More actions
July 15, 2010 at 9:42 am

#224150

Hi All,
I have one job which expects a parameter. Based on parameter the job should run
Ex:
Scenario 1
IF @P_Char ='Y'
BEGIN
SQL Stmt1
SQL Stmt2
SQL Stmt3
SQL Stmt4
END
ELSE IF @P_Char ='N'
BEGIN
SQL Stmt11
SQL Stmt21
SQL Stmt31
SQL Stmt41
END
ELSE
BEGIN
SQL Stmt11
SQL Stmt21
SQL Stmt31
SQL Stmt41
END
Scenario 2:
Exec proj_sp_test @par='Y'
The above procedure should prompt for Parameter.
Thanks,
Sasidhar Pulivarthi

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you cannot get it to prompt for a value. it's not designed that way...it is designed to run unattended.

what you can do is make the job check a value in some table...and update the table with your parameter as to whether it will be Y or N.

declare @P_Char CHAR(1)

SELECT @P_Char = myParameter from master.dbo.MyJobParametersTable

If @P_Char = 'Y'

BEGIN....

if it needs to be prompted, it should be an application which calls the code, instead of a job.

if it is going to be run on demand, you coudl use a stored proceudre, which accepts the @P_Char as a parameters, to do the decision.

CREATE PROCEDURE MyWorkHorse(@P_Char CHAR(1)

AS ....

EXEC MyWorkHorse 'Y'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Shanalise Hall of Fame Points: 3188 More actions · Answer 2

You might as well look at this

http://www.simple-tech.info/how-pass-parameters-variables-and-use-set-values-field-from-a-sql-server-job-to-a-ssis-package.aspx