sqlsql-serversql-server-agentsql-job

SQL Server Agent Job - Exists then Drop?

How can I drop sql server agent jobs, if (and only if) it exists?

This is a well functioning script for stored procedures. How can I do the same to sql server agent jobs?

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[storedproc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[storedproc]
GO
CREATE PROCEDURE [dbo].[storedproc] ...
Solution

  • Try something like this:

    DECLARE @jobId binary(16)

SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N'Name of Your Job')
IF (@jobId IS NOT NULL)
BEGIN
    EXEC msdb.dbo.sp_delete_job @jobId
END

DECLARE @ReturnCode int
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Name of Your Job'

    Best to read the docs on all the parameters required for 'sp_add_job' and 'sp_delete_job'