Multiple separate IF conditions in SQL Server
Solution 1
IF you are checking one variable against multiple condition then you would use something like this Here the block of code where the condition is true will be executed and other blocks will be ignored.
IF(@Var1 Condition1)
BEGIN
/*Your Code Goes here*/
END
ELSE IF(@Var1 Condition2)
BEGIN
/*Your Code Goes here*/
END
ELSE --<--- Default Task if none of the above is true
BEGIN
/*Your Code Goes here*/
END
If you are checking conditions against multiple variables then you would have to go for multiple IF Statements, Each block of code will be executed independently from other blocks.
IF(@Var1 Condition1)
BEGIN
/*Your Code Goes here*/
END
IF(@Var2 Condition1)
BEGIN
/*Your Code Goes here*/
END
IF(@Var3 Condition1)
BEGIN
/*Your Code Goes here*/
END
After every IF statement if there are more than one statement being executed you MUST put them in BEGIN..END Block. Anyway it is always best practice to use BEGIN..END blocks
Update
Found something in your code some BEGIN END you are missing
ELSE IF(@ID IS NOT NULL AND @ID in (SELECT ID FROM Places)) -- Outer Most Block ELSE IF
BEGIN
SELECT @MyName = Name ...
...Some stuff....
IF(SOMETHNG_1) -- IF
--BEGIN
BEGIN TRY
UPDATE ....
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
-- END
ELSE IF(SOMETHNG_2) -- ELSE IF
-- BEGIN
BEGIN TRY
UPDATE ...
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
-- END
ELSE -- ELSE
BEGIN
BEGIN TRY
UPDATE ...
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message'
RETURN -1
END CATCH
END
--The above works I then insert this below and these if statement become nested----
IF(@A!= @SA)
BEGIN
exec Store procedure
@FIELD = 15,
... more params...
END
IF(@S!= @SS)
BEGIN
exec Store procedure
@FIELD = 10,
... more params...
Solution 2
To avoid syntax errors, be sure to always put BEGIN
and END
after an IF
clause, eg:
IF (@A!= @SA)
BEGIN
--do stuff
END
IF (@C!= @SC)
BEGIN
--do stuff
END
... and so on. This should work as expected. Imagine BEGIN
and END
keyword as the opening and closing bracket, respectively.
Solution 3
Maybe this is a bit redundant, but no one appeared to have mentioned this as a solution.
As a beginner in SQL I find that when using a BEGIN
and END
SSMS usually adds a squiggly line with incorrect syntax near 'END'
to END, simply because there's no content in between yet. If you're just setting up BEGIN
and END
to get started and add the actual query later, then simply add a bogus PRINT
statement so SSMS stops bothering you.
For example:
IF (1=1)
BEGIN
PRINT 'BOGUS'
END
The following will indeed set you on the wrong track, thinking you made a syntax error which in this case just means you still need to add content in between BEGIN and END:
IF (1=1)
BEGIN
END
Related videos on Youtube
Jack Thor
Updated on March 03, 2020Comments
-
Jack Thor over 4 years
I have multiple
IF
statements that are independent of each other in my stored procedure. But for some reason they are being nested inside each other as if they are part of one big if statementELSE IF(SOMETHNGZ) BEGIN IF(SOMETHINGY) BEGIN..END ELSE IF (SOMETHINGY) BEGIN..END ELSE BEGIN..END --The above works I then insert this below and these if statement become nested---- IF(@A!= @SA) IF(@S!= @SS) IF(@C!= @SC) IF(@W!= @SW) --Inserted if statement stop here END ELSE <-- final else
So it will be treated like this
IF(@A!= @SA){ IF(@S!= @SS){ IF(@C!= @SC) { IF(@W!= @SW){} } } }
What I expect is this
IF(@A!= @SA){} IF(@S!= @SS){} IF(@C!= @SC){} IF(@W!= @SW){}
I have also tried this and it throws
Incorrect syntax near "ELSE". Expecting "CONVERSATION"
IF(@A!= @SA) BEGIN..END IF(@S!= @SS) BEGIN..END IF(@C!= @SC) BEGIN..END IF(@W!= @SW) BEGIN..END
Note that from
ELSE <--final else
down is now nested insideIF(@W!= @SW)
Even though it is part of the outer if statementELSE IF(SOMETHNGZ)
before.EDIT
As per request my full statement
ALTER Procedure [dbo].[SP_PLaces] @ID int, ..more params AS BEGIN SET NOCOUNT ON DECLARE @SomeId INT ..more varaible SET @SomeId = user define function() ..more SETS IF(@ID IS NULL) BEGIN BEGIN TRY INSERT INTO Places VAlUES(..Values...) ... more stuff... BEGIN TRY exec Store procedure @FIELD = 15, ... more params... END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS 'Message' RETURN -1 END CATCH RETURN 0 END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS 'Message' RETURN -1 END CATCH END ELSE IF(@ID IS NOT NULL AND @ID in (SELECT ID FROM Places)) BEGIN SELECT @MyName = Name ... ...Some stuff.... IF(SOMETHNG_1) BEGIN TRY UPDATE .... END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS 'Message' RETURN -1 END CATCH ELSE IF(SOMETHNG_2) BEGIN TRY UPDATE ... END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS 'Message' RETURN -1 END CATCH ELSE BEGIN BEGIN TRY UPDATE ... END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS 'Message' RETURN -1 END CATCH END --The above works I then insert this below and these if statement become nested---- IF(@A!= @SA) BEGIN exec Stored procedure @FIELD = 15, ... more params... END IF(@S!= @SS) BEGIN exec Stored procedure @FIELD = 10, ... more params... END IF(@C!= @SC) BEGIN exec Stored procedure @FIELD = 17, ... more params... END IF(@W!= @SW) BEGIN exec Stored procedure @FIELD = 12, ... more params... END --Inserted if statement stop here END ELSE BEGIN SET @ResultMessage = 'Update/Delete Failed. No record found with ID:'+CONVERT(varchar(50), @ID) SELECT @ResultMessage AS 'Message' RETURN -1 END Set NOCOUNT OFF END
-
marc_s over 10 yearsSide note: you should not use the
sp_
prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_
and use something else as a prefix - or no prefix at all!
-
-
Jack Thor over 10 yearsI did check the last code block that I posted. It throws an Error.
-
LittleSweetSeas over 10 yearsPlease poste your full statement. Error is elsewhere
-
Jack Thor over 10 yearsAs stated I am already using
BEGIN..END
, but using that actually throws an error. -
M.Ali over 10 years@JackThor using BEGIN..END throws an error ?? it cant be that its something else throwing error. To debug it I would take out all of the code and use PRINT statement in each BEGIN END block something like PRINT 'Block 1' etc etc to check exactly between which blocks your code control is jumping in and out.
-
Jack Thor over 10 yearsUsing the
Begin..END
gives it a red squiggly underline for the last End in the group of If statement that I inserted. And when I hover over it it saysIncorrect syntax near "END". Expecting "CONVERSATION"
-
M.Ali over 10 years@JackThor see my update and you are missing some BEGIN END blocks in you MOST OUTER ELSE IF BLOCK I have added them in comments starting with two -- , ignore the commented ELSE and IF ELSE :)