SQL Server query error

Question

SQL Server query error

Navigation

#1 by (3 votes)

3

I have the following SQL:

INSERT INTO SISCli (CdInscricao,DsEntidade,DsApelido,InInscricao,InCadastro,DsEndereco,NrCEP,NrInscricaoEstadual,NrCGCCPF,NrTelefone,DtCadastro,CdEmpresa,DsEMail,DsUSuarioInc,InClassificacaoFiscal,DsBairro)
SELECT
    case when LEN(ESP353_XML.DES_NrCGCCPF)=13 then '0'+ CAST(ESP353_XML.DES_NrCGCCPF AS VARCHAR)
         when LEN(ESP353_XML.DES_NrCGCCPF)=12 then '00'+ CAST(ESP353_XML.DES_NrCGCCPF AS VARCHAR)
         when LEN(ESP353_XML.DES_NrCGCCPF)=11 then '000'+ CAST(ESP353_XML.DES_NrCGCCPF AS VARCHAR)
         when LEN(ESP353_XML.DES_NrCGCCPF)=10 then '0000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=9  then '00000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=8  then '000000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=7  then '0000000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=6  then '00000000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         else CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
    end AS [CdInscricao],
    ESP353_XML.DES_DsEntidade AS [DsEntidade],
    ESP353_XML.DES_DsApelido AS [DsApelido],
    case when LEN(ESP353_XML.DES_NrCGCCPF)>11 then 0 else 1 end AS [InInscricao],
    0 AS [InCadastro],
    ESP353_XML.DES_DsEndereco AS [DsEndereco],
    ESP353_XML.DES_NrCEP AS [NrCEP],
    'ISENTO' AS [NrInscricaoEstadual],
    case when LEN(ESP353_XML.DES_NrCGCCPF)=13 then '0'+ CAST(ESP353_XML.DES_NrCGCCPF AS VARCHAR)
         when LEN(ESP353_XML.DES_NrCGCCPF)=12 then '00'+ CAST(ESP353_XML.DES_NrCGCCPF AS VARCHAR)
         when LEN(ESP353_XML.DES_NrCGCCPF)=11 then ''+ CAST(ESP353_XML.DES_NrCGCCPF AS VARCHAR)
         when LEN(ESP353_XML.DES_NrCGCCPF)=10 then '0'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=9  then '00'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=8  then '000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=7  then '0000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         when LEN(ESP353_XML.DES_NrCGCCPF)=6  then '00000'+ CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
         else CAST(ESP353_XML.DES_NrCGCCPF AS varchar)
    end AS [NrCGCCPF],
    ESP353_XML.DES_NrTelefone AS [NrTelefone],
    GETDATE() AS [DtCadastro],
    1 as [CdEmpresa],
    '[email protected]' as [DsEMail],
    'EdiXMLMI' AS [DsUSuarioInc],
    7 AS [InClassificacaoFiscal],
    substring(ISNULL(ESP353_XML.DES_DsBairro,0),1,15) as [DES_DsBairro]
FROM ESP353_XML
where NOT exists (select 1 from SISCli A WHERE cast(A.CdInscricao as numeric) = cast(ESP353_XML.DES_NrCGCCPF as numeric))

Running it returns the error below:

Mensagem 512, Nível 16, Estado 1, Procedimento TRG_BloqueioInsereRegInvalido, Linha 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

But if I put a top 1 after SELECT and go running one by one it works.

So far I have not found the error that returns below and why.

sql-server

asked by anonymous 28.07.2017 / 20:50

1 answer

SELECT in VIEW generates subquery? Only allow delete key

score 3 · Accepted Answer

But if I put a top 1 after SELECT and go running one by one it works.

The error is not in the include code but in the trigger procedure TRG_BlockInsertRegInvalid. Because the inclusion code works when you include one row at a time, the TRG_BlockingInsertRegInvalid procedure is probably programmed incorrectly, prepared to receive only one row at a time. As stated in # This is the most common error in triggering T-SQL procedure programming: programming the trigger procedure as if a single line in the virtual tables was received at each procedure call. "

A trigger procedure in T-SQL must be programmed considering that the INSERTED and DELETED virtual tables may have one, one or more rows. If you have trouble reprogramming the TRG_BloqueInsertRegInvalido procedure, I suggest you open another topic with information about the procedure.

Further reading suggestion: Traps in scheduling trigger procedures .

You can simplify the inclusion code. Evaluate the suggestion below.

-- código #1
INSERT INTO SISCli (CdInscricao, DsEntidade, DsApelido, InInscricao, InCadastro, DsEndereco, NrCEP, NrInscricaoEstadual, NrCGCCPF, NrTelefone, DtCadastro, CdEmpresa, DsEMail, DsUSuarioInc, InClassificacaoFiscal, DsBairro)
     SELECT right( ('00000000' + cast(ESP353_XML.DES_NrCGCCPF as varchar)), 14) as [CdInscricao],
            ESP353_XML.DES_DsEntidade AS [DsEntidade],
            ESP353_XML.DES_DsApelido AS [DsApelido],
            case when LEN(ESP353_XML.DES_NrCGCCPF) > 11 then 0 else 1 end AS [InInscricao],
            0 AS [InCadastro],
            ESP353_XML.DES_DsEndereco AS [DsEndereco],
            ESP353_XML.DES_NrCEP AS [NrCEP],
            'ISENTO' AS [NrInscricaoEstadual],
            case when LEN(ESP353_XML.DES_NrCGCCPF) > 11
                 then right( ('00'+ cast(ESP353_XML.DES_NrCGCCPF as varchar)), 14) 
                 else right( ('00000'+ cast(ESP353_XML.DES_NrCGCCPF as varchar)), 11) end as [NrCGCCPF],
            ESP353_XML.DES_NrTelefone AS [NrTelefone],
            current_timestamp AS [DtCadastro],
            1 as [CdEmpresa],
            '[email protected]' as [DsEMail],
            'EdiXMLMI' AS [DsUSuarioInc],
            7 AS [InClassificacaoFiscal],
            substring(ISNULL(ESP353_XML.DES_DsBairro,0),1,15) as [DES_DsBairro]
       from ESP353_XML
       where not exists (select * from SISCli as A 
                         where cast(A.CdInscricao as numeric) = cast(ESP353_XML.DES_NrCGCCPF as numeric));