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 statement
ELSE 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 inside IF(@W!= @SW) Even though it is part of the outer if statement ELSE 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 3 Answers
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... 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.
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 
