3

I use databasemetadata to get columns (read parameters) of a stored procedure on SQL server:

Connection connection = getConnection(); //getting the connection -   
DatabaseMetaData dbMetaData = connection.getMetaData();

HashMap<String, Integer> paramInfo = new HashMap<String, Integer>();
if (dbMetaData != null){
    ResultSet rs = dbMetaData.getProcedureColumns (null, null, sp_name.toUpperCase(), "%");
    while (rs.next())
        paramInfo.put(rs.getString(4), rs.getInt(6));
    rs.close();
}

Does getProcedureColumns() return the procedure columns in an ordered way? Meaning if in the database the stored procedure parameters are- abc(@a int,@b int,@c int), would I always get @a, @b and @c in an ordered way?

If yes, is there any documentation to suggest the same?

HBK
  • 735
  • 1
  • 11
  • 29

1 Answers1

2

All JDBC drivers are required to follow the JDBC specification and API documentation, and the API documentation of DatabaseMetaData.getProcedureColumns specifies:

Retrieves a description of the given catalog's stored procedure parameter and result columns.

Only descriptions matching the schema, procedure and parameter name criteria are returned. They are ordered by PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME and SPECIFIC_NAME. Within this, the return value, if any, is first. Next are the parameter descriptions in call order. The column descriptions follow in column number order.

If a driver does not follow this specification, then that is a bug in the driver. So in general you should be able to rely on above description.

Otherwise, check the values in columns COLUMN_TYPE (index 5) which specifies the type (in parameter, result set column, etc) and ORDINAL_POSITION (index 18) which describes the position (check the details in the javadoc).

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • 1
    Furthermore, the `SQLServerDatabaseMetaData#getProcedureColumns` source code reveals that it calls the SQL Server system stored procedure `sp_sproc_columns`, whose [documentation](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-sproc-columns-transact-sql) says that "The results returned are ordered by PROCEDURE_QUALIFIER, PROCEDURE_OWNER, PROCEDURE_NAME, and the order that the parameters appear in the procedure definition." – Gord Thompson Sep 23 '17 at 13:14