IF EXISTS(SELECT * FROM sysobjects O, sysusers U
          WHERE
            o.name = 'NOVO_CODIGO_SC'  and
            type = 'P'                 and
            u.uid = o.uid              and
            u.name = USER)
  DROP PROCEDURE NOVO_CODIGO_SC
  GO

CREATE PROCEDURE NOVO_CODIGO_SC
   @P_CONTADOR     VARCHAR(100),
   @P_SEQUENCIAL   CHAR(20) OUTPUT,
   @P_QTDE         INT = 1
AS
   DECLARE @V_COUNT INT;
   DECLARE @V_CODIGO VARCHAR(20);
BEGIN
   SET @V_COUNT  = 0;
   SET @V_CODIGO = NULL;

   SELECT @V_COUNT = COUNT(CDCONTADOR)
   FROM NOVOCODIGO
   WHERE CDCONTADOR = @P_CONTADOR;

   IF @V_COUNT > 0 BEGIN
      SELECT @V_CODIGO = NRSEQUENCIAL
      FROM   NOVOCODIGO WITH (UPDLOCK)
      WHERE  CDCONTADOR = @P_CONTADOR;

      UPDATE NOVOCODIGO
      SET NRSEQUENCIAL = RIGHT(REPLICATE('0',20) + RTRIM(CAST((CAST(NRSEQUENCIAL AS INT) + @P_QTDE) AS CHAR(20))),20)
      WHERE CDCONTADOR = @P_CONTADOR;
   END
   ELSE BEGIN
      SET @V_CODIGO = REPLICATE('0',20);

      INSERT INTO NOVOCODIGO (CDCONTADOR, NRSEQUENCIAL)
      VALUES (@P_CONTADOR, RIGHT(REPLICATE('0',20) + RTRIM(CAST(@P_QTDE AS CHAR(20))),20));
   END;

   SET @P_SEQUENCIAL = RIGHT(REPLICATE('0',20) + RTRIM(CAST((CAST(@V_CODIGO AS INT) + 1) AS CHAR(20))),20);
END
GO
