I am doing a simple function with a 1

[PCStorm]

I am doing a simple function with a cursor. I declare the cursor, open the cursor, fetch the cursor. And it compiles fine. I add an if statement right after the fetch and the compiler says error at THEN. I tried different ifs, and different statements within the if. It does not like anything. I would think the actual error was in the code above, except with the if commented out it is fine. with the if it is not.

This is the code. BTW, it is still to be finished. But those compiler errors are so informative I am trying one step at a time :--0

alter procedure lwarner.oeGetNote(@p_grant_dt datetime,@p_opt_prc decimal,@p_plan_num decimal,@p_plan_type decimal,@p_expr_dt datetime,@p_FAS123Group decimal,@p_FAS123CodeS varchar(80))
as
begin
declare @v_Yes varchar(10),
@v_No varchar(10),
@v_Qualifier decimal,
@v_Qual_Code varchar(10)
select @v_Yes='YES'
select @v_No='NO'
declare c_FAS123 dynamic scroll cursor for select Qualifier,ISNULL(qual_code,' ')
from FAS123 as f
where f.grant_dt=@p_grant_dt
and f.opt_prc=@p_opt_prc
and f.expire_dt=@p_expr_dt
and f.qualifier=@p_FAS123Group
and(f.plan_num=@p_plan_num or f.plan_num=9999)
and(f.plan_type=@p_plan_type or f.plan_type=9999)
open c_FAS123
fetch next c_FAS123 into @v_Qualifier, @v_Qual_Code
IF (sqlcode != err_notfound) THEN
close c_fas123
return (@v_no)
end if
return (@v_no)
end

 

[PCStorm]

Well I got around the problem by doing an

if .. begin
end

Still would like to know why it does not work with

if .. then
end if

 

[grega]

if .. then .. endif isn't part of Transact-SQL syntax. Syntax is

IF logical_expression
statements
[ELSE
[IF logical_expression]
statement]

Multiple statements should be enclosed in a BEGIN .. END block.

Greg.