Incorrect syntax near the keyword else in stored procedure

Question

create proc login
    (@id int out,
     @email varchar(30),
     @passsword varchar(30),
     @type varchar(30) out)
as
begin
    select 
        @id = id, @type = type 
    from 
        registration 
    where 
        email = @email 
        and password = @password

    return 0
end
else
begin
    return 1
end

Answer 1

Presumably, you are looking for this:

create procedure usp_login (
    @email varchar(30),
    @passsword varchar(30),
    @id int output,
    @type varchar(30) output
) as
begin
    set @id = NULL;

    select @id = id, @type = type
    from registration
    where email = @email and password = @password;

    return (case when @id is null then 0 else 1 end);
end;

Some notes:

• Your code suggests that you are using unencrypted passwords. That is the first thing you need to learn how to fix.
• else -- as mentioned in the comments -- requires an if.
• When you mix input and output arguments, I prefer to make the output arguments the last arguments. This is a matter of preference. However, I think it is good practice to keep all inputs together and all outputs together -- unless you have a good reason.
• End your statements with semicolons.
• The use of return values from stored procedures can actually be a best practice. However, it is not necessary.