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
Related videos on Youtube
Author by
Sebastian
Updated on June 04, 2022Comments
-
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 about 6 yearsYou 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 about 6 yearsBad 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 about 6 yearsI think you are confusing VB syntax and SQL syntax.
-
JohnRC about 6 yearsI 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 about 6 yearsI 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 about 6 yearsThanks alot! I followed the second solution.