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 m…

Accepted Answer

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 
```

Using Nested IF ELSE statements in sql

Solution

Problem