Create SQL Server Agent job for stored procedure with input parameter

Question

Could you suggest please how to create SQL Server Agent job for a stored procedure that have 1 input parameter?

The procedure is correctly created and i executed it using this code :

EXECUTE dbo.MYProcedure N'2016-02-25';

Is there a way to create a SQL Server Agent job for this procedure that have parameter ?

So i'm trying the basic way that is add this ligne in EXECUTE dbo.MYProcedure N'2016-02-25'; to the window of step in job But the paraméter can change

Motorman

Answer 1

here are the steps

1. in SQL management studio, right click on "SQL Server Agent" under the SQL server which you are connected to.
2. Select New Job.
3. Enter the job name and then click on steps
4. Click on "New" which should be right at the bottom of the screen.
5. Enter step name.
6. Type: keep it selected as Transact SQL
7. Enter : EXECUTE dbo.MYProcedure N'2016-02-25';

Now save it and it should be ready for running manually. If you do want to automate it then open the job by going into the job monitor under SQL Server Agent in SQL management studio and then click on schedule and provide when and how often you would like your job to run.

If you automating the date parameter then add this as your Transact SQL statement:

DECLARE @DATE DATETIME
--Trim out the time so the date is set to 2016/02/25
--and time changes to 00:00 get date will get todays
--date or the run date
SET @DATE = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
EXECUTE dbo.MYProcedure @DATE

Happy coding!!!

Abortion

Yes that work , but i look for if ican automate the input parameter – Motorman

DECLARE "@DATE" DATETIME //Trim out the time so the date is set to 2016/02/25 and time changes to 00:00 // get date will get todays date or the run date SET "@DATE" = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE())) EXECUTE dbo.MYProcedure "@DATE" Remove the "" around the "@date" – Abortion

do you know is there a way when i launch the job i can have POPUP to fil date somthing like that – Motorman

No. If you want an interface to your procedure... a job is not the place to run it. Agent jobs are generally for unattended automation for maintenance. – Laterality