Stored Procedure error - incorrect syntax near else

Question

I'm working on my first stored procedure, and it's showing an error at the else (where I have a comment at the else below). If anyone knows how I can fix the error, I'd appreciate it. I've tried looking online, and I think it may have to do with my begin/end and if/else positioning. I'm having trouble finding a similar example though.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[SPName]
    @userid NVARCHAR(51)
AS 
    SET NOCOUNT ON

    DECLARE @return_status  INT

    IF @userid IS NULL OR @userid = 0
    BEGIN
        --fail
        SET @return_status = 1
    END

    IF  @return_status <> 1
    BEGIN
        IF EXISTS (SELECT login
                   FROM dbo.user_table
                   WHERE (@userid = login))
        --continue because its a good user
        ELSE  
            -- this is where it doesn't like the else
            SET @return_status = 1
    END

    BEGIN
        UPDATE dbo.U_MEMBER
        SET dbo.U_MEMBER.inac = 1,
            dbo.U_MEMBER.reg = @userid
        FROM dbo.U_MEMBER
        WHERE U_MEMBER.VF = 'REV'
    END

    RETURN 0

On line 27 (of that specific set of batches) you start an IF, but you don't have a something to do if it's true. You go straight to an ELSE. An IF need something to do when the IF evaluates to true. — Larnu
On a different note, 3 part naming for columns is to be deprecated/removed from SQL Server. Ideally stick to 2 part naming and aliasing when referencing columns; then you won't be caught out when it happens. — Larnu
Also, is the intention of SET @return_status=1 to fail the SP, because it doesn't. In fact, the second time is it set achieves nothing, as it's never referenced against afterwards. Seems like you might be better off with THROW for the first IF and a custom error message. — Larnu
A foreign key between U_MEMBER and user_table would negate the need for an existence check. There is a lot to be improved in this schema and code. Some investigation into best practices is advised. — SMor

Martin Martin · Accepted Answer · 2020-03-24T16:19:18

The IF statement has nothing to do if it equates to true:

IF  @return_status <> 1
BEGIN
    IF EXISTS(select login from dbo.user_table where  (@userid=login))
        --continue because its a good user
    ELSE  --this is where it doesn't like the else
        set @return_status = 1
    END
...

This is syntactically incorrect. TSQL is expecting a statement to be executed if the IF returns true, and in this case a comment is not sufficient.

If you don't need to do anything if the statement is true, just switch to IF NOT EXISTS instead:

IF  @return_status <> 1
BEGIN
    IF NOT EXISTS(select login from dbo.user_table where  (@userid=login))
    BEGIN
        set @return_status = 1
    END
...

Otherwise if you want to use both the true and false outcomes of the IF statement:

IF  @return_status <> 1
BEGIN
    IF EXISTS(select login from dbo.user_table where  (@userid=login))
    BEGIN
        -- Do something with the true outcome
    END
    ELSE
    BEGIN
        set @return_status = 1
    END
...

Stored Procedure error - incorrect syntax near else

2 Answers