CODEWITHSUNDEEP
coldfusioncoldfusion-9
Nathan Stanford
Nathan Stanford

Reputation: 1394

ColdFusion 9 CFScript Query with multiple id's

How using CFScript can you do a get in value list. below works if I pass in one id but what do you do to pass in multiple id's?

// Get Modules By IDs
function getModulesByIDs(string dsn,required numeric pIDS) {
     // Setup a variable for the Query Result
    var qResult = '';
    // Setup the Query variable
    var q= new query();
    // Add Parameter 
    q.addParam(name="ID", value=arguments.pIDS, cfsqltype="CF_SQL_INTEGER");
    // Create the SQL String
    var sqlString="
            SELECT    ROLEID,
                      ROLENAME,
                      NAME,
                      MODULENAME
            FROM      MODULEROLE
            WHERE     
                      MODULEIDS IN :ID
        ";
    q.setdatasource(arguments.pDsn);
    q.setsql(sqlString);
    qResult=q.execute().getresult();
    return qResult;  
}

Upvotes: 4

Views: 2626

Answers (1)

Leigh
Leigh

Reputation: 28873

Use the list attribute to indicate the value contains multiple id's and add parenthesis to construct a proper IN (..) clause. 

q.addParam(name="ID", value=arguments.pIDS, cfsqltype="CF_SQL_INTEGER", list="true");
...
var sqlString="... WHERE MODULEIDS IN (:ID )";

Upvotes: 7

Related Questions