Raiserror not returning expected value

Question

I am using SQL Server and trying to return an error from a stored procedure to a caller. However it does not seem to work. When debugging the returned value is null.

Error handling:

begin try
    if not exists (select * from dbo.Employees where IsCEO = 1)
    begin
        insert into dbo.Employees (FirstName, LastName, Salary, IsCEO, IsManager, ManagerId, EmployeeRank)
        values (@FirstName, @LastName, @Salary, @IsCEO, @IsManager, @ManagerId, @EmployeeRank)              
    end
end try
begin catch
    return raiserror ('CEO already exists', 15, 1)
end catch

Answer 1

Your issue here is that you never encounter error in this code.

Logic of your code says if CEO doesn't exist, insert employee in the table. If it exists, do nothing. And then you check for errors in this and say CEO already exists which makes no sense.

Seem to me you wanted to do follwing.

begin try
if not exists (select * from dbo.Employees where IsCEO = 1)
begin
    insert into dbo.Employees (FirstName, LastName, Salary, IsCEO, IsManager, ManagerId, 
    EmployeeRank)
    values (@FirstName, @LastName, @Salary, @IsCEO, @IsManager, @ManagerId, 
    @EmployeeRank)              
end
ELSE
BEGIN
raiserror ('CEO already exists', 15, 1)
END
end try
begin catch
    --some code that handles possible errors in code above
end catch