locked
stored procedure if else RRS feed

  • Question

  • User-583959464 posted

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

    Wednesday, March 31, 2021 4:33 AM

All replies

  • 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.

    Wednesday, March 31, 2021 8:47 AM
  • 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

    Wednesday, March 31, 2021 9:10 AM