CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Michele
Michele

Reputation: 3881

Stored Procedure error - incorrect syntax near else

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

Upvotes: 0

Views: 466

Answers (2)

KadoLakatt
KadoLakatt

Reputation: 43

Initialize @return_status variable, for example:

DECLARE @return_status  INT

--Initialize the variable with the expected value
SET @return_status = 0

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

Upvotes: 1

Martin
Martin

Reputation: 16433

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
...

Upvotes: 1

Related Questions