none
Criar uma Procedure Confiável para pegar ou gerar um ID de uma tabela SQL Server RRS feed

  • Pergunta

  • Olá pessoal.

    Já fiz uma postagem aqui no fórum sobre esse assunto, mas agora estou com dúvida sobre como criar uma SP no SQL SERVER (sem o uso do @@Identity, ou outros mecanismos). Digo isso, pois estou colocando em prática o que aprendi nas aulas da facul, mas gostaria de montar algo com base no seguinte esquema:

    1 - Criar uma tabela de sequencias para armazenar os nomes de todas as tabelas do banco juntamente com o último ID Usado.

    2  - O bando será acessado por vários terminais. Imagino que seja necessário um controle pois se dois ou mais usuários estiverem gravando um arquivo ao mesmo tempo, pode dar zica.

    3  - Quando um usuário quiser gravar um registro, a SP consulta na tabela de sequencias  o ultimo ID, gera um novo, grava o registro no banco,  depois atualiza a ID da tabela correspondente lá na tabela de sequencias e devolve para a aplicação o ID Usado. Tudo isso controlando cada transação de inserção dos usuários.

    Dizem que esse esquema é bastante seguro, por isso gostaria de aprendê-lo.

    Alguém possui algum conteúdo para me ajudar ou alguém sabe como implementar e que possa me ajudar?

    Agradeço a todos!!!! :)

    • Tipo Alterado Eduardo.Romero sexta-feira, 27 de março de 2015 13:04 Aguardando teste do usuário
    • Tipo Alterado Eduardo.Romero segunda-feira, 30 de março de 2015 11:14
    quarta-feira, 25 de março de 2015 23:21

Respostas

  • Boa tarde Eduardo.Romero.

    Resolvi o problema: criei uma tabela que armazena o último ID de cada tabela. Criei uma procedure, no qual eu passo como parâmetros o nome da tabela e o campo desejados, e ela se encarrega de pegar o último ID, incrementá-lo e devolver para a procedure que insere os dados no banco. Criei tb outra procedure que atualiza a tabela que contém os últimos IDS.

    Funcionou... Eu coloquei um (XLOCK) na procedure 01 para impedir que dois usuários peguem o mesmo ID simultaneamente e dê problemas.. será que está certo assim?

    ================== Procedure 01====================================

    CREATE PROCEDURE uspGerarIDControle
        @NomeTabela VARCHAR(30),
        @NomeCampo VARCHAR(30),
        @NumeroControle INT OUTPUT
    AS

    BEGIN
        DECLARE @Seq AS INT

        --Cria um ID Sequencial com base na tabela tblTabelaSequencia
        SELECT
            @Seq = UltSeq + 1
        FROM tblTabelaSequencia (XLOCK)
        WHERE NomeTabela = @NomeTabela AND NomeCampo = @NomeCampo;
        SET @NumeroControle = @Seq;
    END

    ================== Procedure 02=====================================

    CREATE PROCEDURE uspAtualizarIDControle
        @NomeTabela VARCHAR(30),
        @NomeCampo VARCHAR(30),
        @NumeroControle INT
    AS

    BEGIN
        --Atualiza o ID na tabela de sequencias
        UPDATE
            tblTabelaSequencia
         SET UltSeq = @NumeroControle
         WHERE NomeTabela = @NomeTabela AND @NomeCampo = @NomeCampo;
    END

    ================== Procedure 03=====================================

    CREATE PROCEDURE uspPessoaFisicaInserir
        
        @NomeTabela VARCHAR(30),
        @NomeCampo VARCHAR(30),
        @Imagem VARBINARY(MAX),
        @Ativo VARCHAR(1),
        @NomeCompleto VARCHAR(100),
        @Apelido VARCHAR(30),
        @DataNascimento DATE,
        @IDSexo INT,
        @EstadoCivil INT,
        @IDNacionalidade INT,
        @IDCidadeNaturalidade INT,
        @IDResidenciaTipo INT,
        @QtdeFilhos INT
        

    AS
    BEGIN

        BEGIN TRY
            BEGIN TRAN
                
                --Consultar IDPessoa no banco e Gerar AutoIncremento
                DECLARE @IDPessoa AS INT;
                EXEC uspGerarIDControle @NomeTabela, @NomeCampo, @IDPessoa OUTPUT

                --Gravar registro na tabela tblPessoa
                INSERT INTO tblPessoa
                (
                    IDPessoa,
                    IDPessoaTipo,
                    DataInclusao,
                    Imagem,
                    Ativo
                 )
                 VALUES
                 (
                    @IDPessoa,
                    1,
                    GETDATE(),
                    @Imagem,
                    @Ativo
                 );

                -- Atualizar o ID sequencialDA da tabela tblPessoa na tabela tblTabelaSequencia
                EXEC uspAtualizarIDControle @NomeTabela, @NomeCampo, @IDPessoa
                
                -- Gravar registro na tabela tblPessoaFisica
                INSERT INTO tblPessoaFisica
                (
                    IDPessoaFisica,
                    NomeCompleto,
                    Apelido,
                    DataNascimento,
                    IDSexo,
                    IDEstadoCivil,
                    IDNacionalidade,
                    IDCidadeNaturalidade,
                    IDResidenciaTipo,
                    QtdeFilhos
                    
                ) VALUES
                (
                    @IDPessoa,
                    @NomeCompleto,
                    @Apelido,
                    @DataNascimento,
                    @IDSexo,
                    @EstadoCivil,
                    @IDNacionalidade,
                    @IDCidadeNaturalidade,
                    @IDResidenciaTipo,
                    @QtdeFilhos                
                );
                --Atualizar o ID sequencial da tabela tblPessoaFisica na tabela tblTabelaSequencia
                EXEC uspAtualizarIDControle 'tblPessoaFisica', 'IDPessoaFisica', @IDPessoa

                SELECT @IDPessoa AS Retorno;

            COMMIT TRAN
        END TRY
        BEGIN CATCH
            ROLLBACK TRAN
            SELECT ERROR_MESSAGE() AS Retorno;
        END CATCH



    END



    • Editado Jalber Romano sábado, 28 de março de 2015 17:56
    • Marcado como Resposta Eduardo.Romero segunda-feira, 30 de março de 2015 11:14
    sábado, 28 de março de 2015 17:55

Todas as Respostas

  • Porque você não usa o auto increment do sql server?
    quarta-feira, 25 de março de 2015 23:35
  • Deleted
    quinta-feira, 26 de março de 2015 09:46
  • Bom dia Alexandre. É que o SQL Server tem uns BUGS, e quando eu deixo o AutoIncrement ligado ele salta um número muito grande, ex: de 3 ele salta para 1009... Gostaria de gerar esses IDs em vez de o banco fazê-lo....
    quinta-feira, 26 de março de 2015 10:36
  • Obrigado Jose.Diz... vc como sempre me salvando rsrsr vou checar e depois retorno. :)
    quinta-feira, 26 de março de 2015 10:37
  • Bom dia Jalber,

    Conseguiu resolver seu problema? Se sim, nos retorne com a solução. Caso negativo, nos de mais detalhes que continuaremos ajudando.

    Obrigado,

    Abraço!


    Eduardo Romero

    Esse conteudo e fornecido sem garantias de qualquer tipo, seja expressa ou implicita.

    MSDN Community Support

    Por favor, lembre-se de Marcar como Resposta as respostas que resolveram o seu problema. Essa e uma maneira comum de reconhecer aqueles que o ajudaram e fazer com que seja mais facil para os outros visitantes encontrarem a resolucao mais tarde.

    sexta-feira, 27 de março de 2015 13:04
  • Boa tarde Eduardo.Romero.

    Resolvi o problema: criei uma tabela que armazena o último ID de cada tabela. Criei uma procedure, no qual eu passo como parâmetros o nome da tabela e o campo desejados, e ela se encarrega de pegar o último ID, incrementá-lo e devolver para a procedure que insere os dados no banco. Criei tb outra procedure que atualiza a tabela que contém os últimos IDS.

    Funcionou... Eu coloquei um (XLOCK) na procedure 01 para impedir que dois usuários peguem o mesmo ID simultaneamente e dê problemas.. será que está certo assim?

    ================== Procedure 01====================================

    CREATE PROCEDURE uspGerarIDControle
        @NomeTabela VARCHAR(30),
        @NomeCampo VARCHAR(30),
        @NumeroControle INT OUTPUT
    AS

    BEGIN
        DECLARE @Seq AS INT

        --Cria um ID Sequencial com base na tabela tblTabelaSequencia
        SELECT
            @Seq = UltSeq + 1
        FROM tblTabelaSequencia (XLOCK)
        WHERE NomeTabela = @NomeTabela AND NomeCampo = @NomeCampo;
        SET @NumeroControle = @Seq;
    END

    ================== Procedure 02=====================================

    CREATE PROCEDURE uspAtualizarIDControle
        @NomeTabela VARCHAR(30),
        @NomeCampo VARCHAR(30),
        @NumeroControle INT
    AS

    BEGIN
        --Atualiza o ID na tabela de sequencias
        UPDATE
            tblTabelaSequencia
         SET UltSeq = @NumeroControle
         WHERE NomeTabela = @NomeTabela AND @NomeCampo = @NomeCampo;
    END

    ================== Procedure 03=====================================

    CREATE PROCEDURE uspPessoaFisicaInserir
        
        @NomeTabela VARCHAR(30),
        @NomeCampo VARCHAR(30),
        @Imagem VARBINARY(MAX),
        @Ativo VARCHAR(1),
        @NomeCompleto VARCHAR(100),
        @Apelido VARCHAR(30),
        @DataNascimento DATE,
        @IDSexo INT,
        @EstadoCivil INT,
        @IDNacionalidade INT,
        @IDCidadeNaturalidade INT,
        @IDResidenciaTipo INT,
        @QtdeFilhos INT
        

    AS
    BEGIN

        BEGIN TRY
            BEGIN TRAN
                
                --Consultar IDPessoa no banco e Gerar AutoIncremento
                DECLARE @IDPessoa AS INT;
                EXEC uspGerarIDControle @NomeTabela, @NomeCampo, @IDPessoa OUTPUT

                --Gravar registro na tabela tblPessoa
                INSERT INTO tblPessoa
                (
                    IDPessoa,
                    IDPessoaTipo,
                    DataInclusao,
                    Imagem,
                    Ativo
                 )
                 VALUES
                 (
                    @IDPessoa,
                    1,
                    GETDATE(),
                    @Imagem,
                    @Ativo
                 );

                -- Atualizar o ID sequencialDA da tabela tblPessoa na tabela tblTabelaSequencia
                EXEC uspAtualizarIDControle @NomeTabela, @NomeCampo, @IDPessoa
                
                -- Gravar registro na tabela tblPessoaFisica
                INSERT INTO tblPessoaFisica
                (
                    IDPessoaFisica,
                    NomeCompleto,
                    Apelido,
                    DataNascimento,
                    IDSexo,
                    IDEstadoCivil,
                    IDNacionalidade,
                    IDCidadeNaturalidade,
                    IDResidenciaTipo,
                    QtdeFilhos
                    
                ) VALUES
                (
                    @IDPessoa,
                    @NomeCompleto,
                    @Apelido,
                    @DataNascimento,
                    @IDSexo,
                    @EstadoCivil,
                    @IDNacionalidade,
                    @IDCidadeNaturalidade,
                    @IDResidenciaTipo,
                    @QtdeFilhos                
                );
                --Atualizar o ID sequencial da tabela tblPessoaFisica na tabela tblTabelaSequencia
                EXEC uspAtualizarIDControle 'tblPessoaFisica', 'IDPessoaFisica', @IDPessoa

                SELECT @IDPessoa AS Retorno;

            COMMIT TRAN
        END TRY
        BEGIN CATCH
            ROLLBACK TRAN
            SELECT ERROR_MESSAGE() AS Retorno;
        END CATCH



    END



    • Editado Jalber Romano sábado, 28 de março de 2015 17:56
    • Marcado como Resposta Eduardo.Romero segunda-feira, 30 de março de 2015 11:14
    sábado, 28 de março de 2015 17:55
  • Deleted
    sábado, 28 de março de 2015 18:43
  • Obrigado pela excelente explicação Jose.Diz.

    Mas, e se dois usuários estiverem inserindo, por exemplo, um mesmo registro? como eu bloqueio um e deixo o outro aguardando? Isso não corre o risco de criar lacunas nos IDs da tblTabelaSequencias não?

    sábado, 28 de março de 2015 22:57