CREATE PROCEDURE CP_Job_Cargar @pDB SYSNAME, @pQuery NVARCHAR(4000), @pFecha DATETIME, @pNombre VARCHAR(100)
AS

DECLARE @jn NVARCHAR(200)
		,@descrip NVARCHAR(100)
		,@fecha int
		,@hora int
		,@fecha_iso NVARCHAR(24)

SET @fecha_iso = CONVERT(NVARCHAR(100),@pFecha,126) 

SET @jn		=  N'CJ_'+ CAST(@pDB as NVARCHAR(100)) + N'_'+ CAST(@pNombre as NVARCHAR(100)) + N'_'+ @fecha_iso
SET @fecha	= CONVERT(INT, LEFT( CONVERT(NVARCHAR(100),@pFecha,112) ,8) )
SET @hora	= CONVERT(INT, SUBSTRING(@fecha_iso,12,2) +  SUBSTRING(@fecha_iso,15,2) + SUBSTRING(@fecha_iso,18,2) ) 



IF EXISTS ( SELECT * FROM msdb.dbo.sysjobs WHERE name = @jn)
BEGIN
	EXEC msdb.dbo.sp_delete_job @job_name = @jn  , @delete_history = 1 , @delete_unused_schedule = 1  
END
   
SET @descrip = N'Ejecuta ' + @jn
EXEC msdb.dbo.sp_add_job  
    @job_name = @jn,
	@description = @descrip;  
  
EXEC msdb.dbo.sp_add_jobstep  
    @job_name =  @jn,  
    @step_name = @descrip,  
    @subsystem = N'TSQL',  
	@database_name = @pDB,
    @command = @pQuery,   
    @retry_attempts = 1,  
    @retry_interval = 1 ;  


IF NOT EXISTS ( SELECT * FROM msdb.dbo.sysschedules WHERE name = @descrip ) 
BEGIN
	EXEC msdb.dbo.sp_add_schedule  
		@schedule_name = @descrip ,  
		@active_start_date  = @fecha,
		@freq_type = 1,  
		@freq_interval = 1,
		@freq_subday_type  = 0x1,
		@active_start_time = @hora


END
 

EXEC msdb.dbo.sp_attach_schedule  
   @job_name = @jn,   
   @schedule_name = @descrip;  

EXEC msdb.dbo.sp_add_jobserver  
    @job_name = @jn 

/*

EXEC CP_Job_Cargar 'Clever_CleverSoft'
        , ' EXEC [dbo].[CPJ_Cbtes_Lotes_Facturar] ''{"Lote":"SOP-2021-12/6","Fecha":"20211201","Descripcion":"SOP-2021-12/6","Id_Lote_Tipo":"1","Fecha_Desde":"20211201","Fecha_Hasta":"20211231","Fecha_Rem_Desde":"        ","Fecha_Rem_Hasta":"        ","Id_Tipo_Cont":"1","Id_Usuario":"-1","FiltrarTarjeta":"0","FiltrarCliente":"0","FiltrarCondPago":"0","FiltrarTipoCbte":"0","FiltrarCantCont":"0","Refacturar":"1","Incl_Cbtes_Cero":"0","FlagUsarTrans":"0","ModoDebug":"0","Omitir_Resultado_OK":"0"}'' '
		,'20211229 12:30'
		, 'Prueba1'

*/


