IF OBJECT_ID('P_GERA_SEQUENCIA') IS NOT NULL
    BEGIN
        DROP PROCEDURE P_GERA_SEQUENCIA;
    END;
GO

CREATE PROCEDURE [dbo].[P_GERA_SEQUENCIA]

    @CDEMP      INT,

    @Tabela     VARCHAR(100),

    @Chave      VARCHAR(100),

    @Criterio   VARCHAR(MAX)

AS

BEGIN

    SET NOCOUNT ON;

    SET XACT_ABORT ON; 
 
    DECLARE

        @SequenciaTabela BIGINT = 0, 

        @MaxExistente BIGINT = 0,  

        @Resultado BIGINT = 0,

        @Sql NVARCHAR(MAX),

        @ParamDefinition NVARCHAR(200),

        @FullTableName NVARCHAR(256),

        @WhereClause NVARCHAR(MAX);
 
    BEGIN TRY


        SET @Criterio = dbo.[F_PADRONIZA_AGRUPAMENTO](@Criterio);
 

        SET @FullTableName = QUOTENAME(@Tabela);


        IF LTRIM(RTRIM(ISNULL(@Criterio, ''))) = ''

            SET @WhereClause = N'';

        ELSE

            SET @WhereClause = N' WHERE ' + @Criterio;
 
        BEGIN TRANSACTION;
 
        SELECT @SequenciaTabela = ISNULL(SEQUENCIA, 0)

        FROM EST_NUM_SEQ_TABELA WITH (UPDLOCK, HOLDLOCK)

        WHERE CD_EMP = @CDEMP

          AND TABELA = @Tabela

          AND CHAVE = @Chave

          AND (AGRUPAMENTO = @Criterio OR (@Criterio IS NULL AND AGRUPAMENTO IS NULL));


        SET @Sql = N'SELECT @OutMax = ISNULL(MAX(' + QUOTENAME(@Chave) + N'), 0) FROM ' + @FullTableName + @WhereClause + N';';

        SET @ParamDefinition = N'@OutMax BIGINT OUTPUT';
 
        EXEC sp_executesql @Sql, @ParamDefinition, @OutMax = @MaxExistente OUTPUT;
 
 
        IF @MaxExistente > @SequenciaTabela

            SET @Resultado = @MaxExistente + 1;

        ELSE

            SET @Resultado = @SequenciaTabela + 1;
 

        IF EXISTS (

            SELECT 1

            FROM EST_NUM_SEQ_TABELA WITH (UPDLOCK, HOLDLOCK)

            WHERE CD_EMP = @CDEMP

              AND TABELA = @Tabela

              AND CHAVE = @Chave

              AND (AGRUPAMENTO = @Criterio OR (@Criterio IS NULL AND AGRUPAMENTO IS NULL))

        )

        BEGIN

            UPDATE EST_NUM_SEQ_TABELA

            SET SEQUENCIA = @Resultado

            WHERE CD_EMP = @CDEMP

              AND TABELA = @Tabela

              AND CHAVE = @Chave

              AND (AGRUPAMENTO = @Criterio OR (@Criterio IS NULL AND AGRUPAMENTO IS NULL));

        END

        ELSE

        BEGIN

            INSERT INTO EST_NUM_SEQ_TABELA (CD_EMP, SEQUENCIA, TABELA, CHAVE, AGRUPAMENTO)

            VALUES (@CDEMP, @Resultado, @Tabela, @Chave, @Criterio);

        END
 
        COMMIT TRANSACTION;
 
        -- retorna resultado

        SELECT @Resultado AS Valor;

        RETURN;

    END TRY

    BEGIN CATCH

        IF XACT_STATE() <> 0

            ROLLBACK TRANSACTION;
 
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();

        DECLARE @ErrorNumber INT = ERROR_NUMBER();

        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();

        DECLARE @ErrorState INT = ERROR_STATE();

        DECLARE @ErrorLine INT = ERROR_LINE();
 
        RAISERROR('P_CONTROLE_SEQUENCIA failed: %s (Number %d, Severity %d, State %d, Line %d)',

                  16, 1, @ErrorMessage, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorLine);

        RETURN;

    END CATCH

END

