IF ELSE STATEMENT in stored procedure

16,626

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
Share:
16,626

Related videos on Youtube

Sebastian
Author by

Sebastian

Updated on June 04, 2022

Comments

  • Sebastian
    Sebastian almost 2 years

    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
    
    • Pred
      Pred about 6 years
      You have 2 CTEs (t1 and t2), but you don't have a query using those afterwards. You must have one and it cannot be an IF statement, it has to be a query (see documentation for details and scope of CTEs).
    • marc_s
      marc_s about 6 years
      Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged
  • JohnRC
    JohnRC about 6 years
    I think you are confusing VB syntax and SQL syntax.
  • JohnRC
    JohnRC about 6 years
    I would recommend always putting ... ELSE NULL before END at the end of your CASE statements to make the default explicitly visible. Beware your code would override any '...DEFAULT..' constraints that would apply to the omitted columns per the INSERT code given in the question.
  • devio
    devio about 6 years
    I like the second solution, as I just browsed thru the answers to look for the solution I had in mind. I wonder how nobody came up with a CROSS JOIN ;)
  • Sebastian
    Sebastian about 6 years
    Thanks alot! I followed the second solution.