stored procedure if else

Question

User-583959464 posted

how can I prepare a stored procedure to have many if else block to check many validation ?

Answer 1

User753101303 posted

Hi,

See https://docs.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql?view=sql-server-ver15

Do you need to use else blocks ? You want to stop at the first problem or to return a value telling about all issues? You could for example have for each validation something like :

IF <not valid condition>
BEGIN
     SET @result=9
     RETURN -- exit the SP
END
-- etc and if you reach the next line, it means all validated fine
INSERT etc... -- do actual stuf

Or  use https://docs.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-operators-transact-sql?view=sql-server-ver15 and before doing the actual stuff do
SET @result=0
IF <not validation condtion> SET @result |= 2 - with a distinct binary flag for each problem
IF <not validation condtion> SET @result |= 4 - etc..
IF @result<>0 RETURN -- if at least a problem exit the SP
-- at this point all validated fine, do actual stuff here
INSERT etc...

In short try to minimize else blocks especially if nested and prefer to exit the SP when you can't anyway go further.

Answer 2

User1535942433 posted

Hi 20141113,

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.

More details,you could refer to below article:

https://stackoverflow.com/questions/20413064/multiple-separate-if-conditions-in-sql-server/20413328

Best regards,

Yijing Sun