Cursor problem: SQL Server

Question

Cursor problem: SQL Server

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

I have a problem with the cursor part: cursor with the name 'BankBank' does not exist.

sql code:

if @Opcao='1'                
  begin                
  set @tipoPagamento='20'                
  set @LayOut='040'                
  declare CursoBanco Cursor LOCAL STATIC for                

     Select distinct ct.BcoNum AS BANCO,                 
      (case when left(ct.BcoNum,3) = '341' then '01'                

          THEN (case WHEN left(empcpfcgc,8)=left(entcpfcgc,8) then '43' else 
      '41' end)                
         else (case WHEN left(empcpfcgc,8)=left(entcpfcgc,8) then '07' else '03' end)                
                    END)                
         END) AS TipoRemesa                

    From PARC_DOC_FIN  PDF With(Nolock),ITEM_COB_BANC ICB 
   With(Nolock),conta_parc_doc_fin ct With(Nolock),                
         Empresa_filial ef With(Nolock), entidade ent with(nolock)          
      Where PDF.EmpCod = ICB.EmpCodparc                    
        And PDF.DocFinChv = ICB.DocFinChv                     
        And PDF.ParcDocFinSeq = ICB.ParcDocFinSeq                    
        And PDF.ParcDocFinDesmPag = ICB.ParcDocFinDesmPag                  
        And ICB.BcoNum = @Banco                    
        And ICB.CobBancRemNum = @Remessa                    
        And left(ICB.EmpCod,2) = left(@Empresa,2)                
        And ICB.CobBancTipo = @Tipo                    
        And ICB.AgNum = @Agencia                    
        And left(ICB.EmpCod,2)=ef.empcod                
        And PDF.entcod=ent.entcod                
        and ct.empcod = icb.empcod             
        and ct.docfinchv = icb.docfinchv                
        and ct.ParcDocFinSeq = icb.ParcDocFinSeq                
       order by TipoRemesa                
    end                


if @Opcao='2'                
 begin                
   set @tipoPagamento='20'                
   set @LayOut='030'                
   declare CursoBanco Cursor LOCAL STATIC for                
   Select distinct left(pdf.parcdocfincodleit,3) AS BANCO, (CASE left(pdf.parcdocfincodleit,3) WHEN '341' THEN '30' ELSE '31' END) AS TipoRemesa                
     From  PARC_DOC_FIN PDF With(Nolock),ITEM_COB_BANC ICB With(Nolock)                    
       Where PDF.EmpCod = ICB.EmpCodparc                    
         And PDF.DocFinChv = ICB.DocFinChv                     
         And PDF.ParcDocFinSeq = ICB.ParcDocFinSeq                    
         And PDF.ParcDocFinDesmPag = ICB.ParcDocFinDesmPag                  
         And ICB.BcoNum = @Banco                    
     And ICB.CobBancRemNum = @Remessa                    
         And left(ICB.EmpCod,2) = left(@Empresa,2)                
         And ICB.CobBancTipo = @Tipo                    
        And ICB.AgNum = @Agencia                    
   order by TipoRemesa                      
 end                

if @Opcao='3'                
begin                

    -- DARF = 22 - 16     GPS = 22 - 17    FGTS/GFIP = 22 - 35                
   set @tipoPagamento='22'                
   set @LayOut='030'                

declare CursoBanco Cursor LOCAL STATIC for                
   Select distinct left(pdf.parcdocfincodleit,3) AS BANCO,                 
       (Case when (docfinespec = 'FGTS') or (pdf.entcod = '0000069') or 
(pdf.entcod = '0002924') then '35'                
             when (docfinespec = 'GPS') or (pdf.entcod = '0002481') then '17'                
                when docfinespec = 'DARF' then '16' end) as TipoRemesa                       
     From  PARC_DOC_FIN PDF With(Nolock),ITEM_COB_BANC ICB With(Nolock), 
    Doc_fin DF With(noLock)                    
       Where PDF.EmpCod = ICB.EmpCodparc                    
         And PDF.DocFinChv = df.DocFinChv                 
          And PDF.EmpCod = Df.EmpCod                 
         And PDF.DocFinChv = ICB.DocFinChv                     
         And PDF.ParcDocFinSeq = ICB.ParcDocFinSeq                    
         And PDF.ParcDocFinDesmPag = ICB.ParcDocFinDesmPag                  
         And ICB.BcoNum = @Banco                    
         And ICB.CobBancRemNum = @Remessa                    
         And ICB.EmpCod = @Empresa                         
         And ICB.CobBancTipo = @Tipo                    
        And ICB.AgNum = @Agencia                    
       order by BANCO                      
      end   


   if @Opcao='4'
   begin
     set @tipoPagamento='20' -- Diversos - FP- 13 Pagto Concessionarias;
     set @LayOut='030'

    declare CursoBanco Cursor LOCAL STATIC for
    Select distinct substring(pdf.parcdocfincodleit,2,1) AS Banco,  '13' as 
    TipoRemessa
      From  PARC_DOC_FIN PDF With(Nolock),ITEM_COB_BANC ICB With(Nolock)
     Where PDF.EmpCod = ICB.EmpCodparc
       And PDF.DocFinChv = ICB.DocFinChv
       And PDF.ParcDocFinSeq = ICB.ParcDocFinSeq
       And PDF.ParcDocFinDesmPag = ICB.ParcDocFinDesmPag
       And ICB.BcoNum = @Banco
       And ICB.CobBancRemNum = @Remessa
       And left(ICB.EmpCod,2) = left(@Empresa,2)
       And ICB.CobBancTipo = @Tipo
       And ICB.AgNum = @Agencia
     order by Banco -- Banco = Segmento = 1-Prefeituras;2-Saneamento;3-Energia Eletrica e Gas;4-Telecomunicações; 5- DARF/GPS com codigo
end

Open CursoBanco

sql sql-server microsoft

asked by anonymous 18.05.2018 / 13:33

2 answers

0

In some languages there is the scope question, where variables declared inside a block (usually for BEGIN END) only exist within the block. But in the case of T-SQL there is this feature.

Considering the error message reported by Felipe, cursor with the name 'CursoBanco' does not exist , and the conditional statement of the CourseBanco cursor (which depends on the value of the variable @Option), most likely that none of the blocks in which the cursor is declared has been executed.

Exemplifying, in the execution of the code below no error occurs:

-- código #1
declare @Opcao int;
set @Opcao= 1;

IF @Opcao = 1
  begin
  declare B cursor local
    for SELECT * from tabela;
  end;

Open B;

But in the execution of code # 2 the same error message mentioned by Felipe is displayed:

-- código #2
declare @Opcao int;
set @Opcao= 2;

IF @Opcao = 1
  begin
  declare B cursor local
    for SELECT * from tabela;
  end;

Open B;

18.05.2018 / 15:12

How to Take the "resizing" of a textarea? How to import packages into other sub packages in Python?

score 1 · Accepted Answer

You can create a variable table and insert the records into it. After that create, out of the ifs and cursor you want:

DECLARE @banco TABLE(banco VARCHAR(10),
                     tipo  VARCHAR(10));

IF @opcao = '1'
BEGIN
  SET @tipoPagamento='20';
  SET @LayOut='040';

  INSERT INTO @banco(banco, tipo)
  // QUERY DA OPÇÃO 1
END;

IF @opcao = '2'
BEGIN
   SET @tipoPagamento = '20';
   SET @LayOut = '030';

  INSERT INTO @banco(banco, tipo)
  // QUERY DA OPÇÃO 2
END;

IF @Opcao='3'
BEGIN
  -- DARF = 22 - 16     GPS = 22 - 17    FGTS/GFIP = 22 - 35
  SET @tipoPagamento = '22';
  SET @LayOut = '030';

  INSERT INTO @banco(banco, tipo)
  // QUERY DA OPÇÃO 3
END;

IF @Opcao = '4'
BEGIN
  SET @tipoPagamento = '20'; -- Diversos - FP- 13 Pagto Concessionarias;
  SET @LayOut = '030';

  INSERT INTO @banco(banco, tipo)
  // QUERY DA OPÇÃO 4
END;

DECLARE CursoBanco CURSOR FOR
  SELECT b.banco,
         b.tipo
    FROM @banco b
Open CursoBanco
FETCH NEXT FROM CursoBanco Into @bancofavorecido, @formapagamento
WHILE (@@FETCH_STATUS = 0)
BEGIN
  // LOGICA DO CURSOR

  FETCH NEXT FROM CursoBanco Into @bancofavorecido, @formapagamento
END;
CLOSE CursoBanco;
DEALLOCATE CursoBanco;