ExecuteSQL with conditional Arguments

[James Gill]

Is there a way for ExecuteSQL to only search provided arguments instead of searching for empty arguments?  

ExecuteSQL ( "SELECT id_contact FROM contacts WHERE contactFirstName = ? AND contactLastName = ? and contactDOB = ? and contactSSN = ? ; "" ; "" ; searchNameFirst ; searchNameLast ; searchDOB ; searchSSN )

I'm trying to write an ExecuteSQL statement where a user can provide a first name, last name, date of birth, and social security number that returns a list of contact ID's that match the provided arguments.  However sometimes DOB and SSN will not be available.  How would I write an ExecuteSQL statement that omits empty search criteria instead of searching for an empty field as in the query above?

[Wim Decorte]

By creating a script that will loop through a set of passed parameters and construct a matching ExecuteSQL() call in a string.  Then use the Evaluate() function on the string...

If you were at Devcon last year you'll find an approach for this in my demo file.

 

[David Jondreau]

Do you mean searchDOB will be empty while contactDOB has a value?

Or searchDOB has a value while contactDOB does not?

[James Gill]

searchDOB would be the global field that the user is typing their search criteria into (same with all of the fields named searchXXX).  ccontactXXX is the field that contains the actual data being searched.

[David Jondreau]

OK, but which one will be left empty? 

Will a user leave a search global empty or will the contact record not have a value for a field? I'm guessing the user may not enter a birthdate when searching, but I want to confirm that.

[James Gill]

The global field may be left empty if the user is unable to provide a SSN or DOB to search for.  However, not all Contacts will have a SSN or DOB available to be searched.

 

So either/or.

Edited May 19, 2015 by James Gill

[David Jondreau]

I would do it this way:

 

/*for example: SELECT id_contact FROM contacts WHERE contactFirstName='Tom' AND contactLastName='Ad' AND ( contactSSN='111-11-1111' OR contactSSN IS NULL )
*/
Let([
select = "SELECT id_contact FROM contacts" ;

where.name = "contactFirstName='" & contacts::searchNameFirst & "' AND contactLastName='" & contacts::searchNameLast & "'";

where.dob = "( contactDOB='" & contacts::searchDOB & "' OR contactDOB IS NULL )"  ;
where.dob = If ( not IsEmpty ( contacts::searchDOB ) ; " AND " & where.dob ) ;
 

where.ssn = "( contactSSN='" & contacts::searchSSN & "' OR contactSSN IS NULL )"  ;
where.ssn = If ( not IsEmpty ( contacts::searchSSN ) ; " AND " & where.ssn ) ;
 

sql = select & " WHERE " & where.name  & where.dob & where.ssn ;

result = ExecuteSQL ( sql ; "" ; "" ) 
];

result

)