Variable is not populated in Procedure

Asked

Viewed 67 times

1

Good morning. I am running the file below, which validates whether the load should run or not, and if it is not possible to run, returns an error message stating why it did not run:

declare @dtAtual date,
        @dtCarga date,
        @hrAtual varchar(8),
        @stCarga varchar(20),
        @flPass bit = 0,
        @dtAtualizacaoYC date,
        @dsMensagemErro varchar(500)

select @dtCarga = max(DT_CARGA) from DBM_ETL..LOG_DISPONIBILIDADE_CARGA_CLIENTES_BOLETOS
select @dtAtual = getdate()
select @dtAtualizacaoYC = MAX(dtCarga) from DBM_YC..ycFatosBoletos

if(@dtAtualizacaoYC < @dtAtual or @dtAtualizacaoYC is null)
begin

    if(@dtAtual = @dtCarga)
    BEGIN

        select @stCarga = STATUS_CARGA FROM LOG_DISPONIBILIDADE_CARGA_CLIENTES_BOLETOS WITH(NOLOCK)
        where DT_CARGA = @dtCarga

        WHILE(@stCarga in ('EM ANDAMENTO','PROCESSANDO'))
        BEGIN

            select @hrAtual = CONVERT(varchar(8),convert(time,getdate()))

            if(@flPass = 0 and @hrAtual >= '12:00:00')
            begin       
                SET @dsMensagemErro = 'O processo está a muito tempo em execução e não há disponibilidade na tabela DBM_ETL.DBO.TMP_CARGA_CLIENTES_BOLETOS. Necessário verificar com o responsável pela atualização.'
                BREAK;
            end

            WAITFOR DELAY '00:05:00'

            select @stCarga = STATUS_CARGA FROM DBM_ETL..LOG_DISPONIBILIDADE_CARGA_CLIENTES_BOLETOS WITH(NOLOCK)
            where DT_CARGA = @dtCarga

        END


        IF(@stCarga LIKE '%CONCLU[ÍI]DO%')
        BEGIN
            SET @flPass = 1
        END 
        ELSE
        BEGIN
            SET @dsMensagemErro = 'O status de processamento na tabela LOG_DISPONIBILIDADE_CARGA_CLIENTES_BOLETOS foi atualizado, porém não está como "CONCLUÍDO". Status atualizado na tabela: '+@stCarga+''
        END                 
    END
    ELSE
    BEGIN       
        SET @dsMensagemErro = 'O processo de Carga da tabela TMP_CARGA_CLIENTES_BOLETOS não foi iniciado, ou o status de processo não foi inserido na tabela LOG_DISPONIBILIDADE_CARGA_CLIENTES_BOLETOS'
    END


end

select @flPass as flPass, @dtCarga as dtCarga, @dsMensagemErro as dsMensagemErro

In this case, the table LOG_DISPONIBILIDADE_CARGA_CLIENTES_BOLETOS is with status_carga = CONCLUÍDO and the date less than the current date.
In that case, you should fall at the last IF, and display the message:

"The Load process of the table TMP_CARGA_CLIENTES_BOLETOS was not started, or process status was not inserted in the table LOG_DISPONIBILIDADE_CARGA_CLIENTES_BOLETOS"

However the variable dsMensagemErro is coming null, is not displaying any message.

Someone would have an idea of why this variable is not being filled in, and the query fits the conditions of the latter IF?

EDIT: I found out what it was, guys. The dtAtualized variableYC was null, because the DT_CARGA field of the DBM_YC table.. ycFatosBoletes was null. I did an update and put the DT_CARGA = current date and it was. I ate ball. Thanks for the answers!

  • but if status_carga = CONCLUÍDO should not fall into the if and leave? the message you mentioned is on else

  • In addition to the above observation of Ricardo, whenever defining the value of a variable from a query it must be verified whether it received any value or not (NULL).

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.