lundi 22 janvier 2018

IF ELSE STATEMENT In Stored procedure

I am trying to write a stored procedure, but I get "Incorrect syntax near ';'" error. I think there is something wrong with the IF Else statements.

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);

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

Aucun commentaire:

Enregistrer un commentaire