Output parameter from SQL Server stored procedure

问题:

I have a problem with the output parameter of a stored procedure when it contains more than 4000 characters. The response seems to be truncated by the JDBC driver? How can I get the full result?

The stored procedure answers with the complete response (> 4000 characters) but I can not open it from Java. I have tried both jTDS and Microsoft's JDBC driver 6.0. Here is my code:

CallableStatement pstmt = con.prepareCall("{call sp_horus_get_consultorios_stv(?)}"); 
pstmt.registerOutParameter(1, -1); 
pstmt.setString(1, ""); 
pstmt.execute(); 
String sp_horus_get_consultorios_stv = pstmt.getString(1);

This works with stored procedures in sybase.

回答1:

I was able to recreate your issue using Microsoft JDBC Driver 6.x. I found that I could avoid the problem by commenting out the setString call:

try (CallableStatement pstmt = conn.prepareCall("{call usp_horus_get_consultorios_stv(?)}")) {
    pstmt.registerOutParameter(1, Types.LONGNVARCHAR); 
    //pstmt.setString(1, "");  // disabled
    pstmt.execute(); 
    String sp_horus_get_consultorios_stv = pstmt.getString(1);
    System.out.println(sp_horus_get_consultorios_stv.length());  // > 4000 characters
}

Unfortunately, that fix did not solve the problem under jTDS 1.3.1. It appears that jTDS still suffers from the limitation described here. So, for jTDS it appears that we have to do something like this:

String sql = 
        "DECLARE @out NVARCHAR(MAX);" +
        "EXEC usp_horus_get_consultorios_stv @out OUTPUT;" +
        "SELECT @out;";
try (
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(sql)) {
    rs.next();
    String sp_horus_get_consultorios_stv = rs.getString(1);
    System.out.println(sp_horus_get_consultorios_stv.length());  // > 4000 characters
}

回答2:

From what I understand, your output parameter is of type NVARCHAR(8000), which is the maximum explicit number allowed, and outputs a 4000 character Unicode string lenght (2 bytes per char).

However, and lucky you, there another possibility : NVARCHAR(MAX), that basically allows an infinite string lenght (well, not infinite, but almost : What is the maximum number of characters that nvarchar(MAX) will hold?

You should change your output paramater type to NVARCHAR(MAX).

Happy coding ;)