/****** Object:  StoredProcedure [dbo].[P_GLB_SCHEDULE_JOB]    Script Date: 04/15/2013 20:28:08 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_GLB_SCHEDULE_JOB]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_GLB_SCHEDULE_JOB]
GO
GO

CREATE PROCEDURE P_GLB_SCHEDULE_JOB   
WITH ENCRYPTION
AS


DECLARE @SQLName varchar(200); 
DECLARE @SQLString nvarchar(1000); 
DECLARE @CD_CTR INT; 
SET NOCOUNT ON;
DECLARE cQuerys CURSOR FAST_FORWARD FOR 
SELECT
	NM_SCHEDULE,
	CD_CTR,
	STEP_SCRIPT
FROM 
	GLB_SCHEDULE_JOB 
WHERE
	STS=0
	AND DT_HR <=GETDATE(); 

OPEN cQuerys
FETCH NEXT FROM cQuerys INTO @SQLName, @CD_CTR,@SQLString; 
	WHILE @@FETCH_STATUS=0
	  begin
		
		begin try
			PRINT 'Executando a rotina ' + convert(varchar(max),@CD_CTR) + ' - ' + @SQLName + ' - ' + convert(varchar,getdate(),108)
			
			if exists(select 1 from GLB_SCHEDULE_JOB 
						where cd_ctr=@CD_CTR
						and STS=0)
			begin
				UPDATE T
				SET STS=1,
				    Msg= 'Processing' -- em execucao
				FROM GLB_SCHEDULE_JOB T
				WHERE CD_CTR =@CD_CTR;

				EXECUTE sp_executesql  @SQLString;  

				UPDATE T
				SET STS=3,
				    Msg= 'Sucess' --Finish
				FROM GLB_SCHEDULE_JOB T
				WHERE CD_CTR =@CD_CTR;

			end
			
		END TRY
		BEGIN CATCH
			--IF @@NESTLEVEL > 0  ROLLBACK TRANSACTION;
			declare @mensagemErro  varchar(1500)
			SELECT	@mensagemErro = ERROR_MESSAGE();
			 
			PRINT 'Falha na rotina ' + convert(varchar(max),@CD_CTR) + ' - ' + @SQLName + ' - ' + convert(varchar,getdate(),108)
			UPDATE T 
			 SET STS=2,
				MSG=@mensagemErro
			FROM
				GLB_SCHEDULE_JOB T
			WHERE CD_CTR =@CD_CTR;

		END CATCH

		FETCH NEXT FROM cQuerys INTO @SQLName, @CD_CTR,@SQLString;
	end

PRINT 'Concluido. ' + convert(varchar,getdate(),108)
CLOSE cQuerys;
DEALLOCATE cQuerys;
