SQL Server Performance

CURSOR getting parameters to execute internal PROCEDURES

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by andreluis1411, Jul 9, 2009.

  1. andreluis1411 New Member

    Hi... I have a cursor and it gets four dates I´m saving them onto four scalar variables. But when I try to use them as parameters to execute some procedures, I´m getting the problems listed bellow.Msg 102, Level 15, State 1, Line 62
    Incorrect syntax near '@FINAL_PERIOD'.
    Msg 137, Level 15, State 2, Line 1
    Must declare the scalar variable "@INITIAL_DATE".
    ==============================================================================================================================
    Here I posted the cursor I designed... I hope some of you can help me. Thanks./* Declaration of the Variables */declare
    @INITIAL_DATE as datetime;declare
    @FINAL_DATE as datetime;declare
    @INITIAL_PERIOD as datetime;declare
    @FINAL_PERIOD as datetime;/* Select the initial date */set
    @INITIAL_DATE = (
    select convert(datetime,
    convert(varchar(04), substring(description, 7, 4)) + '-' +
    convert(varchar(02), substring(description, 4, 2)) + '-' +
    convert(varchar(02), substring(description, 1, 2))) from gconsistwhere codcoligada
    = 1 and aplicaction = 'C' and tablecode = 'REFERENCIA'
    and clientcode = 1)/* Select the final date */set @FINAL_DATE =
    (
    select convert(datetime,
    convert(varchar(04), substring(description, 7, 4)) + '-' +
    convert(varchar(02), substring(description, 4, 2)) + '-' +
    convert(varchar(02), substring(description, 1, 2))) from gconsistwhere codcoligada
    = 1 and aplicaction = 'C' and tablecode = 'REFERENCIA'
    and clientcode = 2)/* Select all the DATES to execute how many times as necessary all the procedures */declare
    UPDAT_ALL_SALES cursor for(
    select convert(datetime,
    convert(varchar(04), substring(description, 7, 4)) + '-' +
    convert(varchar(02), substring(description, 4, 2)) + '-' +
    convert(varchar(02), substring(description, 1, 2))) fromgconsistwherecodcoligada = 1 and aplicacao = 'C'
    and codtabela = 'FATURAMENT')open
    UPDAT_ALL_SALESfetch
    next from UPDAT_ALL_SALES into @FINAL_PERIODwhile
    @@fetch_status = 0begin/* Built the initial period based on final period */set
    @INITIAL_PERIOD = convert(datetime,convert
    (varchar(04), datepart(year, @FINAL_PERIOD)) + '-' +convert
    (varchar(02), datepart(month, @FINAL_PERIOD)) + '-01')/* 9 Procedures to be executed after got the needed dates */exec FAT_1_SALDOANTERIOR @INITIAL_DATE, @FINAL_DATE, @INITIAL_PERIOD, @FINAL_PERIOD
    goexec FAT_2_MENSAL @INITIAL_DATE, @FINAL_DATE, @INITIAL_PERIOD, @FINAL_PERIOD
    goexec FAT_3_BAIXAS @INITIAL_DATE, @FINAL_DATE, @INITIAL_PERIOD, @FINAL_PERIOD
    goexec FAT_4_ANTEC_FAT_MES_BX_ANT @INITIAL_DATE, @FINAL_DATE, @INITIAL_PERIOD, @FINAL_PERIOD
    goexec FAT_5_ANTEC_FAT_POST_BX_MES @INITIAL_DATE, @FINAL_DATE, @INITIAL_PERIOD, @FINAL_PERIOD
    goexec FAT_6_ACORDOS_TOTAIS @INITIAL_DATE, @FINAL_DATE, @INITIAL_PERIOD, @FINAL_PERIOD
    goexec FAT_7_ACORDOS_FAT_POST_BX_MES @INITIAL_DATE, @FINAL_DATE, @INITIAL_PERIOD, @FINAL_PERIOD
    goexec FAT_8_ACORDOS_FAT_MES_BX_ANT @INITIAL_DATE, @FINAL_DATE, @INITIAL_PERIOD, @FINAL_PERIOD
    goexec FAT_9_SALDO_ANTERIOR @INITIAL_DATE, @FINAL_DATE, @INITIAL_PERIOD, @FINAL_PERIOD
    go/* Go to next date if exists */fetch
    next from UPDAT_ALL_SALES into @FINAL_PERIODend
    close
    UPDAT_ALL_SALESdeallocate
    UPDAT_ALL_SALES
  2. Adriaan New Member

    Simple enough - drop the GO after each EXEC. It's not like you have to do EXEC ... GO to get the statement executed - EXEC does that all by itself.
    The only function of GO is to signal the end of a batch. And any variable exists only within the batch in which it is declared.

Share This Page