Simpler to use the built-in db functions (see include/func/func.db.php) and table references ($sql_tbl):
PHP Code:
$qry = "SELECT clean_url FROM $sql_tbl[clean_urls] WHERE resource_id IN (".implode(',',$prodId).')';
$urls = func_query_first($qry);
func_query_column() is another good one that might be more of what you are looking for.
A more thorough reply might be to loop the productids and call func_get_resource_url() (see func.clean_urls.php), though that is more resource intensive, but should be fine for small lists of products.