TRANSACT-SQL: DECLARED variable generating compilation error

Question

It's been more than 10 years ago since I last did coding in SQL and I know I may not be updated on the updates.

I am getting compile errors such as:

03:41:30  FAILED  [FETCH - 0 rows, 0.012 secs]  [Code: 137, SQL State: S1000]  Must declare the scalar variable "@v_doc_number". 

03:41:30  FAILED  [WHILE - 0 rows, 0.013 secs]  1) [Code: 137, SQL State: S1000]  Must declare the scalar variable "@v_ref_doc_type".
2) [Code: 137, SQL State: S1000]  Must declare the scalar variable "@v_dt_doc_date".  


03:41:30  FAILED  [UPDATE - 0 rows, 0.014 secs]  [Code: 137, SQL State: S1000]  Must declare the scalar variable "@v_iYear".  

2) [Code: 137, SQL State: S1000]  Must declare the scalar variable "@v_ref_doc_type".
3) [Code: 137, SQL State: S1000]  Must declare the scalar variable "@v_dt_doc_date".  

03:41:30  FAILED  [UPDATE - 0 rows, 0.013 secs]  [Code: 137, SQL State: S1000]  Must declare the scalar variable "@v_iYear".

I know I declared the variables properly since they are right there at the top...

The following code was written on a basic text file, and pasted to DBVisualizer SQL Commander. when I tried to run the code.. the compile errors pop up..

I had a similar cursor/variable script from 10 years ago and the syntax is all the same and I don't have a clue on how to correct this... the difference between 10 years ago and now.. is that before. I was using Windows SQL tools on a Win98 system... while now. I am doing these on a Mac / DBVisualizer tool... not sure if this is environment related (have to set flags and all)...

thanks in advance for the help... this is just the first step in a tool I will be using...

code / batch script follows...


USE qerpclean;

DECLARE @v_doc_number AS varchar(12),
        @v_ref_doc AS varchar(12),
    @v_ref_ext_num AS varchar(12),
    @v_ref_doc_type AS varchar(12),
    @v_dt_doc_date AS datetime,
    @v_iYear AS integer,
    @v_iYrWkNum AS integer,
    @v_iQuarter AS integer,
    @v_iMonth AS integer,
    @v_iMnWkNum AS integer,
    @v_s_Long_Mn_Name AS varchar(12),
    @v_s_Short_Mn_Name AS varchar(12),
    @v_iDOW AS integer,
    @v_iDOW2 AS integer;

DECLARE source_detail CURSOR FOR 
SELECT s_dr_number, 
    s_ref_doc_number,
    s_ref_ext_number,
    s_ref_doc_type
FROM dr_source_detail;

OPEN source_detail;

FETCH NEXT FROM source_detail INTO @v_doc_number,
                  @v_ref_doc,
                  @v_ref_ext_num,
                  @v_ref_doc_type; 

WHILE @@FETCH_STATUS = 0 
    BEGIN

        IF @v_ref_doc_type = 'PA'
        BEGIN

            SELECT @v_dt_doc_date = dt_doc_date,
                @v_iYear = iYear,
                @v_iYrWkNum = iYrWkNum,
                @v_iQuarter = iQuarter,
                @v_iMonth = iMonth,
                @v_iMnWkNum = iMnWkNum,
                @v_s_Long_Mn_Name = s_Long_Mn_Name,
                @v_s_Short_Mn_Name = s_Short_Mn_Name,
                @v_iDOW = iDOW,
                @v_iDOW2 = iDOW2
            FROM pa_header
            WHERE s_pa_number = @v_ref_ext_num ;

                         UPDATE dr_source_detail
            SET dr_source_detail.i_kss_sl_Year = @v_iYear ,
                dr_source_detail.i_kss_sl_YrWkNum = @v_iYrWkNum,
                dr_source_detail.i_kss_sl_Quarter = @v_iQuarter,
                dr_source_detail.i_kss_sl_Month = @v_iMonth,
                dr_source_detail.i_kss_sl_MnWkNum = @v_iMnWkNum,
                dr_source_detail.s_kss_Long_Mn_Name = @v_s_Long_Mn_Name, 
                dr_source_detail.s_kss_Short_Mn_Name = @v_s_Short_Mn_Name,
                dr_source_detail.i_kss_DOW = @v_iDOW,
                dr_source_detail.i_kss_DOW2 = @v_iDOW2
            WHERE s_dr_number = @doc_number
                AND s_ref_doc_number = @v_ref_doc
                AND s_ref_ext_number = @v_ref_ext_num;


        END 

        IF @v_ref_doc_type = 'SO'
        BEGIN
            SELECT s_dr_number,
                        @v_dt_doc_date = dt_doc_date,
                        @v_iYear = iYear,
                        @v_iYrWkNum = iYrWkNum,
                        @v_iQuarter = iQuarter,
                        @v_iMonth = iMonth,
                        @v_iMnWkNum = iMnWkNum,
                        @v_s_Long_Mn_Name = s_Long_Mn_Name,
                        @v_s_Short_Mn_Name = s_Short_Mn_Name,
                        @v_iDOW = iDOW,  
                        @v_iDOW2 = iDOW2
            FROM dr_header
            WHERE s_dr_number = @doc_number;

                         UPDATE dr_source_detail
            SET dr_source_detail.i_kss_sl_Year = @v_iYear ,
                dr_source_detail.i_kss_sl_YrWkNum = @v_iYrWkNum,
                dr_source_detail.i_kss_sl_Quarter = @v_iQuarter,
                dr_source_detail.i_kss_sl_Month = @v_iMonth,
                dr_source_detail.i_kss_sl_MnWkNum = @v_iMnWkNum,
                dr_source_detail.s_kss_Long_Mn_Name = @v_s_Long_Mn_Name, 
                dr_source_detail.s_kss_Short_Mn_Name = @v_s_Short_Mn_Name,
                dr_source_detail.i_kss_DOW = @v_iDOW,
                dr_source_detail.i_kss_DOW2 = @v_iDOW2
            WHERE s_dr_number = @v_doc_number
                AND s_ref_doc_number = @v_ref_doc
                AND s_ref_ext_number = @v_ref_ext_num;


        END 



    END 

CLOSE source_detail;

DEALLOCATE source_detail;

TRANSACT-SQL: DECLARED variable generating compilation error

Answers (1)

Related Questions