Hi all, does anyone know how I could use Exposed to model a routine/function in MS SQL that takes in a varchar and returns a table? Ideally, I'd love to use the routine in a join statement. Worst case I'll use custom SQL, but was hoping there was a better way.

That sounds like a case/when? What does the SQL look like?

A simplified version of the SQL looks like this:

select a.* from account a
  join Routine('user_id') as rt on a.id = rt.id;

Basically the routine returns a list of account ids the user is allowed to access. Unfortunately, I have to use the routine and don't have access to the inner workings.

Oh, the routine is in the database

one trick I saw is that you define a table and override

describe

then you can join it. So something like

object RoutineUserTable : Table() {    
    override fun describe(s: Transaction, queryBuilder: QueryBuilder) {
        queryBuilder.append("Routine('user_id')") 
    }
}

and this can be joined. I’ve seen this in one of the open github issues

That's very helpful, thanks. The only problem I have is that the

'user_id'

could be different each time I use the table.

you could figure out a way where you can extract

user_id

out as a parameter at a higher level, then create the table dinamically:

fun createTable(param: String): Table() = object: Table() {
    override fun describe(...) {
        queryBuilder.append("Stuff(${param})")
    }
}

That's really interesting to think about. I'm wondering if I could actually handle this by creating a class instead of lots of objects like this:

class FunctionBasedTable(private val inputValue: String) : Table("schema.Function('$inputValue')") {
	val outputValue = varchar("output_column", 10)
}

Tricky tricky! Good find.