Trouble calling a MS SQL Stored Procedure with OUT Parameter using Groovy

Question

When calling this MS-SQL stored procedure:

CREATE PROCEDURE [dbo].[AddUser] 

@networkID varchar(512), 
@id uniqueidentifier OUTPUT 
....

With this Groovy code:

networkAliasID = 'aschoewe_test' 
sql.call("{call AddUser(?, ${Sql.VARCHAR}) }", [networkAliasID]) { s -> 
   println "userID: ${s}" 
}

I receive this error:

java.sql.SQLException: Incorrect syntax near '.'.

This is because it doesn't like the 'Sql.' part of the code code (even though all examples I've seen does it like this). If I change the code to:

networkAliasID = 'aschoewe_test' 
String userID = null 
sql.call("{call AddUser(?, ?) }", [networkAliasID, userID]) { s -> 
   println "userID: ${s}" 
   println "userID: ${userID}" 
}

The code executes, but I can't get the new user ID. Both 's' and 'userID' are null when the code finished executing.

Am I doing something systematically wrong? Is this an issue with MSSQL? Is this an issue with the return type being UNIQUEIDENTIFIER? I'm using the JTDS drivers and the documentation shows that it's JDBC equiavalent type is VARCHAR.

Any help is appreciated,

Andrew

Answer 1

The type of the output parameter should go in the parameter list, not the query string, like so:

networkAliasID = 'aschoewe_test'
sql.call("{call AddUser(?, ?) }", [networkAliasID, Sql.VARCHAR]) { s -> 
    println "userID: ${s}"
}