[Wale]

Hi,

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 it...gives 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

AS
BEGIN
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
calculation
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)
BEGIN
-- If the year is less than CY then the inflation rate is 1
IF (@year < @CY)
BEGIN
SELECT @current_year = 1
END -- if @year < @CY
ELSE
BEGIN
-- 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 ' +
'WHERE PBS_KEY = ' + CONVERT(VARCHAR(12), @PBS) +
' 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)
BEGIN
close cur_inf_rate
return
END -- if 2

ELSE IF (@@sqlstatus = 0)
BEGIN
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

[shuchi]

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

cheers

-s

[Wale]

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.