which is the correct way to delete multiple jobs in sql server

Viewed 9922

Im using SQL Server 2012 and I have a case where I have to delete sql jobs. I found two approaches which are as follows:

Approach 1

DECLARE @jobId binary(16)  

WHILE (1=1)
BEGIN
    SET @jobId = NULL
    SELECT TOP 1 @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name like N'Abc%') 

    IF @@ROWCOUNT = 0
        BREAK

    IF (@jobId IS NOT NULL) 
    BEGIN     
        EXEC msdb.dbo.sp_delete_job @jobId 
    END 
END

Approach 2

DECLARE @listStr VARCHAR(MAX)=null

SELECT @listStr = COALESCE(@listStr+'exec msdb.dbo.sp_delete_job ' ,'') + '''' + convert(varchar(max),job_id) + '''; '
FROM msdb.dbo.sysjobs WHERE (name like N'$(TestPublisherServer)-$(TestPublisherDB)%') 

IF @listStr is not null
BEGIN 
    PRINT 'exec msdb.dbo.sp_delete_job ' + @listStr
    EXEC ('exec msdb.dbo.sp_delete_job ' + @listStr)
END

Both the approaches will delete the jobs, but I want to know which is the best way or you can suggest me more efficient or correct ways to delete the jobs.

And one more question is, do we have to stop/disable the job before deleting it.

TIA Harsha

1 Answers
Related