0

This is the cursor:

  Declare   @CMD VARCHAR(MAX),
            @CMD1  VARCHAR(MAX),
            @CMD2  VARCHAR(MAX),
            @MSG VARCHAR(MAX),
            @TABELA VARCHAR(100),
            @NOME_TABELA VARCHAR(100),
            @NOME_COLUNA VARCHAR(100),
            @TIPO_DADOS VARCHAR(100),
            @LARGURA INT,
            @PRECISAO INT,
            @ESCALA INT,
            @ANULAVEL CHAR(1),
            @ORDEM_COLUNA INT

DECLARE C_TABELAS CURSOR FOR  
    SELECT NAME
        FROM SYSOBJECTS A
        WHERE TYPE = 'U' 
        ORDER BY 1
DECLARE C_NOMES_TABELAS CURSOR FOR
    SELECT NOME_TABELA 
    FROM NOMES_TABELAS 
    ORDER BY NOME_TABELA    

DECLARE C_DETALHES CURSOR FOR 
SELECT NOME_TABELA,NOME_COLUNA,TIPO_DADOS,LARGURA,PRECISAO,ESCALA,ANULAVEL,ORDEM_COLUNA 
FROM COLUNAS_TABELAS 
ORDER BY NOME_TABELA    

  BEGIN
  SET @CMD = ''

  SET @CMD = 'DELETE FROM NOMES_TABELAS';
  EXECUTE (@CMD)
  print @CMD

  SET @CMD = 'DELETE FROM COLUNAS_TABELAS';
 EXECUTE (@CMD)
  print @CMD


  OPEN C_TABELAS   
  FETCH NEXT FROM C_TABELAS INTO @TABELA

  WHILE @@FETCH_STATUS = 0   
    BEGIN   
      EXECUTE GERA_DICIONARIO_TABELA @TABELA,@CMD1 output ,@CMD2 output

      print @CMD1
      print @CMD2
      EXECUTE (@CMD1)
      EXECUTE (@CMD2)

      FETCH NEXT FROM C_TABELAS INTO @TABELA
    END  

  CLOSE C_TABELAS   
  DEALLOCATE C_TABELAS

  ------------------------------------------------------------------
  OPEN C_NOMES_TABELAS   
  FETCH NEXT FROM C_NOMES_TABELAS INTO @TABELA

  WHILE @@FETCH_STATUS = 0   
    BEGIN   
      SET @CMD1 = 'INSERT INTO NOMES_TABELAS (NOME_TABELA,OFICIAL) VALUES (' + '''' + @TABELA + '''' + ',1)' 
      print @CMD1
      FETCH NEXT FROM C_NOMES_TABELAS INTO @TABELA
    END  

  CLOSE C_NOMES_TABELAS   

  -------------------------------------------------------------------

  OPEN C_DETALHES
  FETCH NEXT FROM C_DETALHES INTO @NOME_TABELA,@NOME_COLUNA,@TIPO_DADOS,@LARGURA,
                                  @PRECISAO ,@ESCALA,@ANULAVEL,@ORDEM_COLUNA 

  WHILE @@FETCH_STATUS = 0   
    BEGIN   
      SET @CMD1 = 'INSERT INTO COLUNAS_TABELAS (NOME_TABELA,NOME_COLUNA,TIPO_DADOS,LARGURA,PRECISAO,ESCALA,ANULAVEL,ORDEM_COLUNA) VALUES (' 
      SET @CMD1 = @CMD1 + '''' + @NOME_TABELA + '''' + ','
      SET @CMD1 = @CMD1 + '''' + @NOME_COLUNA + '''' + ','
      SET @CMD1 = @CMD1 + '''' + @TIPO_DADOS + '''' + ','
      IF (@LARGURA IS NOT NULL)  SET @CMD1 = @CMD1 +         @LARGURA + ',' ELSE SET @CMD1= @CMD1 + 'NULL' + ','
      IF (@PRECISAO IS NOT NULL) SET @CMD1 = @CMD1 +         @PRECISAO + ',' ELSE SET @CMD1= @CMD1 + 'NULL' + ',' 
      IF (@ESCALA IS NOT NULL) SET @CMD1 = @CMD1 +         @ESCALA + ',' ELSE SET @CMD1= @CMD1 + 'NULL' + ',' 
      SET @CMD1 = @CMD1 + '''' + @ANULAVEL + '''' + ','
      SET @CMD1 = @CMD1 +  @ORDEM_COLUNA  + ');'
      print @CMD1

      FETCH NEXT FROM C_DETALHES INTO @NOME_TABELA,@NOME_COLUNA,@TIPO_DADOS,@LARGURA,
                                  @PRECISAO ,@ESCALA,@ANULAVEL,@ORDEM_COLUNA 
    END  

  CLOSE C_DETALHES   
  DEALLOCATE C_DETALHES
  DEALLOCATE C_NOMES_TABELAS
  -------------------------------------------------------------------
   end

It does what it needs to do, it loops through 2 tables and populate it with some data. OK. But when it reaches the last table, it gives me an error, trying to insert the first table's data again:

...
INSERT INTO NOMES_TABELAS (NOME_TABELA,OFICIAL) VALUES ('TRANSACOES_TMP',1)
INSERT INTO NOMES_TABELAS (NOME_TABELA,OFICIAL) VALUES ('USUARIO_SISTEMA',1)
INSERT INTO NOMES_TABELAS (NOME_TABELA,OFICIAL) VALUES 
('VALOR_ACP_CONTRAC_PAIS',1)

Msg 245, Level 16, State 1, Procedure GERA_RELEASE, Line 88
FAIL TO CONVERT THE VARCHAR VALUE  'INSERT INTO COLUNAS_TABELAS (NOME_TABELA,NOME_COLUNA,TIPO_DADOS,LARGURA,PRECISAO,ESCALA,ANULAVEL,ORDEM_COLUNA) VALUES ('ARQUIVO_CLIENTE','ARQUIVO_COMPLETO','CHAR',' to data type int.

this ARQUIVO_CLIENTE table was already inserted. why is the cursor not stopping in the last table? Should I deallocate it earlier?

If I print @@FETCH_STATUS, I can see the last -1, but there's no result.

  • Funny part is: When I comment every execute, and only print the commands, it stops in the last table. – Racer SQL Dec 1 '17 at 11:50
2

Your error gives me a clue as to what might be happening.

FAIL TO CONVERT THE VARCHAR VALUE... to data type int.

You have several integer variables that you are trying to concatenate to the @CMD string variable

@CMD1 + @LARGURA

Try using Cast or Convert against your integer variables before trying to concatenate them with the @CMD string varible.

  • Nice, I declared them as varchar. I should have thought about it haha. thanks – Racer SQL Dec 1 '17 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.