CODEWITHSUNDEEP
sql-servergroovyexecute
Longrisko
Longrisko

Reputation: 21

Execute procedure with SQL statement with parameter by groovy

Client SQL Procedure starts like this and they are wainting for value: @Firma

EXECUTE @RC = [dbo].[Aktualizace_stavba_data]

@Firma 

GO

GROOVY SCRIPT - which I need to edit with your help:

import de.uplanet.scripting.groovy.util.Safely

def conn  = g_dbConnections["IPOS20016"]

String Firma= g_request.get("Firma"); //prepared value

//now I dont know how to implement and also write correctly the rest of the script.

def strCall = "{call inc(2,3)}"

def call = conn.prepareCall(strCall)
call.execute()

def rs = call.getResultSet()
def contentProc

while (rs.next()){
contentProc = rs.getInt(1)
}

rs = Safely.close(rs)
stmt = Safely.close(call)

Upvotes: 1

Views: 599

Answers (1)

ou_ryperd
ou_ryperd

Reputation: 2133

I used the following to call a stored proc that takes a parameter and returns one VARCHAR value. In Groovy you generally don't use resultSet.

conn.query("{? = call dbo.Aktualizace_stavba_data(?)}", [Sql.out(Sql.VARCHAR.type), 'parm1']) { returned ->
    println "returned: ${returned}"
}

EDIT: I removed the first '? = ' from the query, it was a mistake. Official documentation here: https://docs.groovy-lang.org/latest/html/documentation/sql-userguide.html#_stored_procedures

Upvotes: 1

Related Questions