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