Filter numbers not yet registered via SQL

Emerson,

To check which codes have not been registered in the range, you can run the query below by changing the table name.

DECLARE @CONTADOR INTEGER
DECLARE @TT INTEGER

CREATE TABLE #CODIGO (
CODIGO INTEGER )

SET @CONTADOR = 1


WHILE ((SELECT MAX(CODIGO) FROM TABELA) >= @CONTADOR)
BEGIN
    SET @TT = (SELECT COUNT(CODIGO) FROM TABELA WHERE CODIGO = @CONTADOR)

IF @TT = 0
BEGIN
INSERT INTO #CODIGO VALUES (@CONTADOR)
END

SET @CONTADOR = @CONTADOR + 1

END

SELECT * FROM #CODIGO
DROP TABLE #CODIGO

It would even be simple to do this:

Do a search on the same table before adding a new record and get the return and add the code in an array and then at the time of add compare if the code entered by the user is inside this array.

It's a simple way to do it.

If you're using SEQUENCE you can predict what the next codigo to be inserted.

CREATE SEQUENCE codigoUsuario;

To select the code:

SELECT nextval('codigoUsuario');

When creating your table you can set SEQUENCE directly in the field:

CREATE TABLE tabela ( codigo INTEGER DEFAULT nextval('codigoUsuario') );

I do not think it's a good idea to leave the code control in the user's hand.

Needing to check if a certain new code already exists at creation time will only create more complexity in the application, which without a really do-able use case is simply unnecessary.

SELECT linha
  FROM (SELECT DECODE(codigo,linha,'N','S') AS  proximo
              ,linha
                 FROM (SELECT codigo
                             ,ROWNUM linha
                             ,nome 
                         FROM cliente
                        ORDER BY codigo))                          
 WHERE proximo = 'S'
   AND ROWNUM = 1

The above example I did with oracle, it makes the next number valid. If it helped, mark it for sure, thanks.