Exposed: How to call a MSSQL stored procedure and iterate over returned result set?

[vasily-kirichenko]

I try

transaction {
    exec("exec dbo.Foo @p = 1) {
        sequence {
        while (it.next()) {
            yield(it.getString("name"))
        }
    }
}

and got the following error:

SQL: exec dbo.Foo @p = 1
[ForkJoinPool.commonPool-worker-1] INFO Exposed - Transaction attempt #0 failed: com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.. Statement(s): exec dbo.Foo @p = 1
org.jetbrains.exposed.exceptions.ExposedSQLException: com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.
SQL: [exec Foo @p = 1]
    at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed(Statement.kt:61)
...

[vasily-kirichenko]

I've ended up with the following function that seems to work:

    fun <T : Any> Transaction.execSp(stmt: String, transform: (ResultSet) -> T): T? {
        if (stmt.isEmpty()) return null

        return exec(object : Statement<T>(StatementType.SELECT, emptyList()) {
            override fun PreparedStatement.executeInternal(transaction: Transaction): T? {
                executeQuery()
                return resultSet?.use { transform(it) }
            }

            override fun prepareSQL(transaction: Transaction): String = stmt
            override fun arguments(): Iterable<Iterable<Pair<ColumnType, Any?>>> = emptyList()
        })
    }

[acrane13]

I'd like to add to this to make it a little more generic to also handle call statements with multiple input and output parameters. For example:
{ call schema.proc_name(?, ?, ?, ?) }
I have stored procedures like this that have 3 input parameters, 1 output parameter, and also return a result set.

[Tapac]

In the next release, exec will be treated the same way as SELECT. Also, providing explicit StatementType for exec functions was added

[Tapac]

Explicit statementType for Transaction.exec functions (also, EXEC StatementType was introduced) available since 0.32.1.