IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'CP_Job_Borrar') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE CP_Job_Borrar 
END
GO

CREATE PROCEDURE CP_Job_Borrar @pNombre NVARCHAR(max)
AS

DECLARE		@job_id			NVARCHAR(max)
			,@step_id		NVARCHAR(max)
			,@sched_id		INT
			,@Lote			VARCHAR(20)
			,@err			INT
			,@rtaMsjStored VARCHAR(100)


BEGIN TRY
	BEGIN TRAN

	SET @rtaMsjStored = ''


	SELECT	@Lote = CL.Lote, @job_id = [sJOB].[job_id] 
		, @step_id = [sJSTP].[step_id] 
		, @sched_id = [sSCH].schedule_id
	FROM  [msdb].[dbo].[sysjobs] AS [sJOB]
		LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
		ON [sJOB].[originating_server_id] = [sSVR].[server_id]
		LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
		ON [sJOB].[category_id] = [sCAT].[category_id]
		LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
		ON [sJOB].[job_id] = [sJSTP].[job_id]
		AND [sJOB].[start_step_id] = [sJSTP].[step_id]
		LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
		ON [sJOB].[owner_sid] = [sDBP].[sid]
		LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
		ON [sJOB].[job_id] = [sJOBSCH].[job_id]
		LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
		ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
		LEFT JOIN Cbtes_Lotes_Trabajos AS [CLT]
		ON CLT.job_id = [sJOB].job_id
		LEFT JOIN Cbtes_Lotes AS [CL]
		ON CLT.Lote = CL.Lote
		WHERE [sJOB].[name] = @pNombre


		IF @Lote	IS NOT NULL
			DELETE [dbo].[Cbtes_Lotes_Trabajos] WHERE [Lote] = @Lote

		IF @sched_id IS NOT NULL
		BEGIN
			EXEC @err = msdb.dbo.sp_delete_schedule @schedule_id = @sched_id ,  @force_delete = 1
			IF @err <> 0
				RAISERROR ('An error occurred while trying to delete the job ', 16,  1 ) 
		END


		IF @step_id IS NOT NULL
		BEGIN
			EXEC @err = msdb.dbo.sp_delete_jobsteplog @job_name = @pNombre  
			IF @err <> 0
				RAISERROR ('An error occurred while trying to delete the job step' , 16,  1 ) 

			EXEC @err = msdb.dbo.sp_delete_jobstep @job_name = @pNombre , @step_id = @step_id
			IF @err <> 0
				RAISERROR ('An error occurred while trying to delete the job step' , 16,  1 ) 
		END


		IF @job_id IS NOT NULL
		BEGIN
			EXEC @err = msdb.dbo.sp_delete_job @job_name = @pNombre  , @delete_history = 1 , @delete_unused_schedule = 1
			IF @err <> 0
				RAISERROR ('An error occurred while trying to delete the job ', 16,  1 ) 
		END
	COMMIT
 								
	SELECT	 0				AS ErrorNumber 
			,0				AS ErrorSeverity  
			,0				AS ErrorState 
			,0				AS ErrorProcedure  
			,0				AS ErrorLine		
			,@rtaMsjStored	AS ErrorMessage


END TRY  


BEGIN CATCH  
    EXECUTE usp_GetErrorInfo

	ROLLBACK

	RETURN 9999
END CATCH

RETURN 0
/*

EXEC CP_Job_Borrar 'CJ_Clever_Macabi_CSA_2022-04__2022-04-18T15:30:00'

*/


