Selecting from database using multiple conditions (SQL)

Hi everyone,

How can I fetch data from the database with multiple conditions that tend to change in my loop.

For example, for one this is what i do

ProjectName = XXX;
a = strcat('''', ProjectName, '''');
curs = ['SELECT ID from Table1 WHERE ProjectName =',a]; cursVessel  = fetch(conn,curs);

Now if I want the ID numbers where my ProjectName = XXX, and Country = YYY; How can I do that?. My project name and country would change within a loop that's why I can't say

curs = ['SELECT ID from Table1 WHERE ProjectName =''XXX'' and Country = ''YYY''']; cursVessel  = fetch(conn,curs);

Thank you in advance,

If any clarifications is needed, please let me know.

Answer 1

[colordepth]

To fetch data from a database with dynamic conditions, you can use either a simple approach for a few conditions or a scalable approach for many optional filters. Both methods use parameterized queries to safely handle variable inputs.

For a simple case with two conditions (e.g., ProjectName and Country), you can conditionally build the query:

ProjectName = 'XXX';  % Required
Country = '';         % Optional (set to 'all' or empty to skip)

if strcmp(Country, 'all') || isempty(Country)
    query = 'SELECT ID FROM Table1 WHERE ProjectName = ?';
    params = {ProjectName};
else
    query = 'SELECT ID FROM Table1 WHERE ProjectName = ? AND Country = ?';
    params = {ProjectName, Country};
end

sqlStatement = databasePreparedStatement(conn, query);
sqlStatement = bindParamValues(sqlStatement, 1:length(params), params);
cursVessel = fetch(conn, sqlStatement);

This works well for a small number of conditions but becomes unwieldy with many optional filters. For a scalable solution, refer to the attached script (dynamicClauseSQL.m). The scalable approach dynamically constructs the WHERE clause based on active filters. It handles any number of conditions and automatically skips empty or 'all' values.