SQL Server: I wonder what syntax error is near else-CodePudding

I'm tring to create a procedure. And when I set @age after setting @flag, something is wrong. But either I tried to delete set @age or set @flag it would work.

error:there is a syntax error near else

I wonder what caused this. Thanks for help.

select * from sc
if exists(select *
                    from sysobjects
                    where name='pro5' and type ='P')
    drop procedure pro5
go

create procedure pro5 @stuNo char(10),@flag int output
as 
declare @age int
begin
        set @age=0
        set @flag=1
        if exists(select *
                  from sc
                  where sno=@stuNo)
            set @flag=0
            set @age=0
        else
                delete 
                from student
                where sno=@stuNO

end
return @age
go

declare @flag int
exec pro5 '991102',@flag output
if @flag=1
    print 'success'
else
    print 'failure'

CodePudding user response：

You need to define a begin and end around all lines in your if statement which by default only assumes a single line belongs to the "if" block:

if exists(select * from sc where sno=@stuNo)
begin
    set @flag=0
    set @age=0
end
else
    delete 
    from student
    where sno=@stuNO

However you could simplify a little since you don't need to also set age which is already 0. I'm assuming you actualy might need to set @age to 1 and this is a cut-down example of your code and you can amend as required:

create procedure pro5 @stuNo char(10), @flag int output
as 
declare @age int = 0;
    
if exists(select * from sc where sno = @stuNo)
  set @flag = 0
else
begin
  delete from student where sno = @stuNO;
  set @flag = 1;
end

return @age;

CodePudding user response：

changed syntax a bit:

create procedure pro5 (
   @stuNo char(10),
   @flag int output
)
as 

begin
  declare @age int
        set @age=0
        set @flag=1
        if exists(select *
                  from sc
                  where sno=@stuNo)
          begin
            set @flag=0
            set @age=0
           end
        else
                delete 
                from student
                where sno=@stuNO

return @age
end

go

declare @flag int
exec pro5 '991102',@flag output
if @flag=1
    print 'success'
else
    print 'failure'