Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002

    Unanswered: What is wrong with using the SET command in a sybase proc


    ASE 11.5, NT
    I am trying to use the SET syntax instead of a SELECT syntax to define a dynamic variable. The Proc blows up when it gets to executing the variable if I use a SELECT syntax to define it. However I an trying to ues the SET syntax instead and it doesn't like this error:

    "Server Message: Number 102, Severity 15
    Incorrect syntax near '@V_CUR'."

    what am I doing wrong? I have pasted the proc below

    create proc dbo.calculate_inflation_rates --CREATE PROC dbo.calculate_inflation_rates
    @PBS varchar(32),
    @ver numeric(4),
    @var numeric(4),
    @cycle_key numeric(2),
    @Year_limit numeric(5)

    -- 2035 or 2070 depending on value in opp_project.post_closure_act

    DECLARE @year numeric
    DECLARE @CY numeric
    DECLARE @current_year numeric(6,3)
    DECLARE @inf_rate numeric(6,3)
    DECLARE @py_inf_rate numeric(6,3)
    DECLARE @V_CUR varchar(255)

    --Create a table to hold the results of the inflation rate
    Create table #results (Year numeric(5) not null,
    inflation_rate numeric(6,3) null)

    --Determine what year to start calculating from by getting the CY value (FY-1) from tx_version
    SELECT @CY = FY-1 FROM tx_version WHERE ver_id = @ver and var_id = @var and cycle_key = @cycle_key

    --Start with 1997
    SELECT @year = 1997

    -- While the year being calculated for is less than the Year_limit execute the following code
    WHILE (@year <= @Year_limit)
    -- If the year is less than CY then the inflation rate is 1
    IF (@year < @CY)
    SELECT @current_year = 1
    END -- if @year < @CY
    -- if the year is greater than CY then calculate the inflation rate by:
    -- getting the inflation rate for the given pbs from opp_escalation
    -- getting the calculated inflation rate from #results for the previous year
    -- multiplying the previous year's inflation rate * (1 + the inflation rate from opp_escalation)

    issue is right here >>>>>SET @V_CUR = 'DECLARE cur_inf_rate CURSOR FOR SELECT isnull(FY' + convert(varchar(4), @year) + ',0) ' +
    'FROM opp_escalation ' +
    ' AND ver_id = ' + CONVERT(VARCHAR(2), @ver) +
    ' AND var_id = ' + CONVERT(VARCHAR(2), @var)

    --DECLARE cur_inf_rate CURSOR FOR SELECT isnull(FY2004,0) FROM opp_escalation WHERE PBS_KEY = 1 AND ver_id = 1 AND var_id = 1
    --PRINT @V_CUR
    exec @V_CUR

    OPEN cur_inf_rate
    FETCH cur_inf_rate INTO @inf_rate

    IF (@@sqlstatus = 2)
    close cur_inf_rate
    END -- if 2

    ELSE IF (@@sqlstatus = 0)
    SELECT @py_inf_rate = isnull(inflation_rate,1) from #results where year = @year - 1

    SELECT @current_year = @py_inf_rate * (1 + @inf_rate)
    -- close and deallocate the cursor
    CLOSE cur_inf_rate
    DEALLOCATE CURSOR cur_inf_rate
    END -- if 0
    END -- else

    -- store the year and the results of the calculation in #results
    INSERT INTO #results (year, inflation_rate)
    VALUES (@year, @current_year)

    --increment the year
    --print convert(varchar(4), @year)
    DECLARE @message varchar(255)
    SELECT @message = convert(varchar(4), @year)
    SELECT @message = @message + ':' + convert(varchar(15), @current_year)
    -- SELECT @message = @message + '; ' + convert(varchar(145), @v_cur)
    print @message
    SELECT @year = @year + 1
    END -- while

    SELECT * FROM #results
    DROP TABLE #results
    END -- procedure
    Last edited by Wale; 03-31-03 at 17:02.

  2. #2
    Join Date
    Jan 2003
    As far as my limited knowledge goes sybase does not accept set command...use select @var=value only. I dont see how using select wud make any difference in anyway since you have used ti everywhere else
    Hope it helps..u can look up soem sybase documentaions for more help but i thnk this cud be the reason for the error


    You try and try again..but then give up, there's no sense in being a complete fool about it!!!

  3. #3
    Join Date
    Mar 2002
    I thought so. I had used the SELECT command in a rough draft of the proc and sent it over to a developer. She returned it to me using the SET command which I thought was for MS Sql server only, anyways you confirmed it for me. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts