IF ELSE STATEMENT in stored procedure

Question

I am trying to write a stored procedure, but I get an error

Incorrect syntax near ';'

I think there is something wrong with the IF Else statements.

CREATE PROCEDURE setSystemStaff
    @SYSTEMNAME nvarchar(50),
    @STAFFNAME nvarchar(50),
    @SYSTEMSTAFFROLE nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    WITH t1 (SYSTEMID) AS 
    (
        SELECT SYSTEMID 
        FROM SYSTEM 
        WHERE SYSTEMNAME = @SYSTEMNAME
    ), t2 (STAFFID) AS 
    (
        SELECT STAFFID 
        FROM STAFF 
        WHERE STAFFNAME = @STAFFNAME
    );

    IF @SYSTEMSTAFFROLE = 'Owner'
        INSERT INTO SYSTEMSTAFF ([SYSTEMID], [SYSTEMSTAFFOWNER])    
            SELECT t1.SYSTEMID, t2.STAFFID 
            FROM t1, t2
    ELSE IF @SYSTEMSTAFFROLE = 'Specialist'
        INSERT INTO SYSTEMSTAFF ([SYSTEMID], [SYSTEMSTAFSPECIALIST])    
            SELECT t1.SYSTEMID, t2.STAFFID 
            FROM t1,t2  
    ELSE 
        RETURN  
END
GO

Answer 1

You Can Re-write the Procedure Using Case Statements, without using the IF..ELSE

CREATE PROCEDURE setSystemStaff
-- Add the parameters for the stored procedure here
@SYSTEMNAME nvarchar(50),
@STAFFNAME nvarchar(50),
@SYSTEMSTAFFROLE nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
WITH t1 
(
    SYSTEMID
) 
AS 
(
    SELECT 
        SYSTEMID  
        FROM SYSTEM 
            WHERE SYSTEMNAME = @SYSTEMNAME
),
t2
(
    STAFFID
) 
AS 
(
    SELECT 
        STAFFID 
        FROM STAFF 
            WHERE STAFFNAME = @STAFFNAME
);
INSERT INTO SYSTEMSTAFF
(
    [SYSTEMID],
    [SYSTEMSTAFFOWNER],
    [SYSTEMSTAFSPECIALIST]
)
SELECT
    SYSTEMID = t1.SYSTEMID,
    SYSTEMSTAFFOWNER = CASE WHEN @SYSTEMSTAFFROLE = 'Owner'
                                THEN t2.STAFFID END,
    SYSTEMSTAFSPECIALIST = CASE WHEN @SYSTEMSTAFFROLE = 'Specialist'
                                THEN t2.STAFFID END
    FROM T1,T2

END
GO

And you can Also Simplyfy the Procedure Like this

CREATE PROCEDURE setSystemStaff
-- Add the parameters for the stored procedure here
@SYSTEMNAME nvarchar(50),
@STAFFNAME nvarchar(50),
@SYSTEMSTAFFROLE nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here

INSERT INTO SYSTEMSTAFF
(
    [SYSTEMID],
    [SYSTEMSTAFFOWNER],
    [SYSTEMSTAFSPECIALIST]
)
SELECT
    SYSTEMID = t1.SYSTEMID,
    SYSTEMSTAFFOWNER = CASE WHEN @SYSTEMSTAFFROLE = 'Owner'
                                THEN t2.STAFFID END,
    SYSTEMSTAFSPECIALIST = CASE WHEN @SYSTEMSTAFFROLE = 'Specialist'
                                THEN t2.STAFFID END
    FROM SYSTEM T1
        INNER JOIN STAFF T2
            ON T1.SYSTEMNAME = @SYSTEMNAME
                AND T2.STAFFNAME = @STAFFNAME

END
GO

Answer 2

I think you are missing 'then' keyword. You should put this condition like

IF then --Statement (Insert /update/delete as required) elsif then --Statement (Insert /update/delete as required)

end if;

--Above syntax used in SQL only

Answer 3

Alternatively: pass to temporary table.

    CREATE PROCEDURE setSystemStaff
    -- Add the parameters for the stored procedure here
        @SYSTEMNAME NVARCHAR(50) ,
        @STAFFNAME NVARCHAR(50) ,
        @SYSTEMSTAFFROLE NVARCHAR(50)
    AS
        BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
            SET NOCOUNT ON;
    -- Insert statements for procedure here

            WITH    t1
                      AS ( SELECT   SYSTEMID
                           FROM     SYSTEM
                           WHERE    SYSTEMNAME = @SYSTEMNAME
                         ),
                    T2
                      AS ( SELECT   STAFFID
                           FROM     STAFF
                           WHERE    STAFFNAME = @STAFFNAME
                         ),
                    T3
                      AS ( SELECT   t1.SYSTEMID ,
                                    t2.STAFFID
                           FROM     t1 ,
                                    t2
                         )
                SELECT  *
                INTO    #temptbl
                FROM    t3


            IF @SYSTEMSTAFFROLE = 'Owner'
                BEGIN
                    INSERT  INTO SYSTEMSTAFF
                            ( [SYSTEMID] ,
                              [SYSTEMSTAFFOWNER]
                            )
                            SELECT  SYSTEMID ,
                                    STAFFID
                            FROM    #temptbl
                END 
            ELSE
                IF @SYSTEMSTAFFROLE = 'Specialist'
                    BEGIN
                        INSERT  INTO SYSTEMSTAFF
                                ( [SYSTEMID] ,
                                  [SYSTEMSTAFSPECIALIST]
                                )
                                SELECT  SYSTEMID ,
                                        STAFFID
                                FROM    #temptbl
                    END

            RETURN

        END
    GO

Answer 4

Please try this-

CREATE PROCEDURE setSystemStaff
(
    @SYSTEMNAME nvarchar(50),
    @STAFFNAME nvarchar(50),
    @SYSTEMSTAFFROLE nvarchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
    IF @SYSTEMSTAFFROLE = 'Owner'
    BEGIN
        WITH t1 AS 
        (
            SELECT SYSTEMID  FROM SYSTEM WHERE SYSTEMNAME = @SYSTEMNAME
        )
        ,t2 AS 
        (
            SELECT STAFFID FROM STAFF WHERE STAFFNAME = @STAFFNAME
        )
        INSERT INTO SYSTEMSTAFF ([SYSTEMID],[SYSTEMSTAFFOWNER]) 
        SELECT t1.SYSTEMID, t2.STAFFID FROM t1,t2
    END
    ELSE IF @SYSTEMSTAFFROLE = 'Specialist'
    BEGIN
        WITH t1 AS 
        (
            SELECT SYSTEMID  FROM SYSTEM WHERE SYSTEMNAME = @SYSTEMNAME
        )
        ,t2 AS 
        (
            SELECT STAFFID FROM STAFF WHERE STAFFNAME = @STAFFNAME
        )
        INSERT INTO SYSTEMSTAFF ([SYSTEMID],[SYSTEMSTAFSPECIALIST]) 
        SELECT t1.SYSTEMID, t2.STAFFID FROM t1,t2  
    END
END
GO