Cursor problem: SQL Server

Question

I have a problem with the cursor part: cursor with the name 'Cursobanco' 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

Answer 1

You can create a variable table and insert the records into it. After this create, outside the ifs the cursor desiring:

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;

Answer 2

In some languages there is the question of scope, in which variables declared within a block (usually even BEGIN END) only exist within the block. But in the case of T-SQL there is no such feature.

Considering the error message informed by Felipe, cursor with the name 'CursoBanco' does not exist, and the conditional declaration of the Cursobanco cursor (which depends on the value of the @Option variable), most likely none of the blocks where the cursor is declared have been executed.

For example, 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 when executing code #2 is displayed the same error message quoted by Felipe:

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

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

Open B;