Using Nested IF ELSE statements in sql

Question

When any one of the following conditions is met, I want the code to go to the next execution step:

First Name, Last Name and DOB : all three are not blank
ID and DOB are not blank
SSN and DOB are not blank
ID and Group Number are not blank

Below is the code I have. When I run it by providing First Name, Last Name and DOB (condition 1 is satisfied), it still fails saying condition 4 is not met. Can someone tell me what am I doing wrong?

IF ( ( @FirstName IS NULL 
        OR Len(Ltrim(@FirstName)) = 0 ) 
     AND ( @LastName IS NULL 
            OR Len(Ltrim(@LastName)) = 0 ) 
     AND ( @DOB IS NULL ) ) 
  BEGIN 
      INSERT INTO @ValidationError 
                  (errormessage) 
      VALUES      ( 'first name, last name and Date of Birth must be specified.' 
      ) 
  END 
ELSE 
  BEGIN 
      IF ( @DOB IS NULL 
           AND @Id IS NULL ) 
        BEGIN 
            INSERT INTO @ValidationError 
                        (errormessage) 
            VALUES      ( 'Date of Birth and Id must be specified.' ) 
        END 
      ELSE 
        BEGIN 
            IF ( @DOB IS NULL 
                 AND @SSN IS NULL ) 
              BEGIN 
                  INSERT INTO @ValidationError 
                              (errormessage) 
                  VALUES      ( 'Date of Birth and SSN must be specified.' ) 
              END 
            ELSE 
              BEGIN 
                  IF ( @Id IS NULL 
                       AND @GroupNumber IS NULL ) 
                    BEGIN 
                        INSERT INTO @ValidationError 
                                    (errormessage) 
                        VALUES      ( 'Id and Group Number must be specified.' ) 
                    END 
              END 
        END 
  END

I tried following the two answers below, but couldn't work it out. Instead, I am updating a parameter for every insert into the @ValidationError table and then using GOTO label if the parameter is not updated (which would mean that nothing was inserted in the table) GOTO is a old habit of mine that I just remembered, from the days of FORTRAN programming. — , Feb 17 '14 at 19:42

Hart CO · Answer 1 · 2014-01-31T21:25:01.450

A CASE statement would be simpler:

INSERT INTO @ValidationError  (errormessage)  
SELECT CASE WHEN Criteria1 THEN 'first name, last name and Date of Birth must be specified.'
            WHEN Criteria2 THEN 'Date of Birth and Id must be specified.'
            WHEN Criteria3 THEN 'Date of Birth and SSN must be specified.'  
            WHEN Criteria4 THEN 'Id and Group Number must be specified.'        
       END

As far as the error in your syntax, you've got extraneous BEGIN and END, I believe the following will work:

IF ( ( @FirstName IS NULL OR Len(Ltrim(@FirstName)) = 0 ) 
     AND ( @LastName IS NULL OR Len(Ltrim(@LastName)) = 0 ) 
     AND ( @DOB IS NULL ) ) 
    BEGIN 
          INSERT INTO @ValidationError 
                      (errormessage) 
          VALUES      ( 'first name, last name and Date of Birth must be specified.') 
    END 
ELSE IF ( @DOB IS NULL AND @Id IS NULL ) 
    BEGIN 
        INSERT INTO @ValidationError 
                    (errormessage) 
        VALUES      ( 'Date of Birth and Id must be specified.' ) 
    END 
ELSE IF ( @DOB IS NULL AND @SSN IS NULL ) 
    BEGIN 
        INSERT INTO @ValidationError 
                    (errormessage) 
        VALUES      ( 'Date of Birth and SSN must be specified.' ) 
    END 
ELSE IF ( @Id IS NULL AND @GroupNumber IS NULL ) 
    BEGIN 
        INSERT INTO @ValidationError 
                    (errormessage) 
        VALUES      ( 'Id and Group Number must be specified.' ) 
    END

score 0 · Answer 2 · answered Jan 31 '14 at 21:31

First, your logic is failing because you have if/then/elseif. In other words, if something has a first name, last name, and date of birth, then they pass the first criteria. What do you do? You go and test the next one. Nope. They passed, so you want to continue.

You are testing for failing all the criteria, not for failing one of them. Your error messages reflect this. There aren't four error messages. There is just one. Basically, it is the concatenation of all of yours, because none of the conditions would be met.

The structure should be:

if (criteria1 failse) {
    if (criteria2 fails) {
        if (criteria3 fails) {
            if (criteria4 fails) {
                everything failse
            }
        }
    }
}

See, if nothing passes, then you can't arbitrarily choose which one fails.

You can wrap this into a single query:

insert into @ValidationError(errormessage)  
    SELECT 'You need to specify one of the following: '+
           'first name, last name and Date of Birth must be specified; ' +
           'Date of Birth and Id must be specified; ' +
           'Date of Birth and SSN must be specified; ' + 
           'Id and Group Number must be specified.'        
    from (select (case when not ( ( @FirstName IS NULL OR Len(Ltrim(@FirstName)) = 0 ) AND
                              ( @LastName IS NULL OR Len(Ltrim(@LastName)) = 0 ) AND
                              ( @DOB IS NULL )
                            )
                       then 'Criteria1'
                       when not ( @DOB IS NULL AND @Id IS NULL ) 
                       then 'Criteria2'
                       when not ( @DOB IS NULL AND @SSN IS NULL )
                       then 'Criteria3'
                       when not ( @Id IS NULL AND @GroupNumber IS NULL )
                  end) as whichsuccess
         ) t
    where whichsuccess is null;

Using Nested IF ELSE statements in sql

2 Answers2