JDBC Driver for SQL Server

Build 20.0.7587

Procedure Parameters

You can use the DatabaseMetaData interface to retrieve stored procedure information. The getProcedureColumns method returns descriptions of stored procedure parameters. You can restrict the results by the stored procedure name.

The following code example outputs information about the parameters of the SearchSuppliers stored procedure:

String connectionString = "jdbc:sql:user=myuser;password=mypassword;Server=localhost;Database=Northwind;";

Connection conn = DriverManager.getConnection(connectionString);
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs=meta.getProcedureColumns(null, null, "SearchSuppliers", null);  
while(rs.next()) {   
  for(int i=1;i<=rs.getMetaData().getColumnCount();i++)  {
    System.out.println(rs.getMetaData().getColumnName(i) +"="+rs.getString(i));
  }
}
The getProcedureColumns method returns the following columns:

Column NameData TypeDescription
PROCEDURE_CATStringThe catalog that the procedure belongs to.
PROCEDURE_SCHEMStringThe schema that the procedure belongs to.
PROCEDURE_NAMEStringThe name of the stored procedure.
COLUMN_NAMEStringThe name of the procedure column.
COLUMN_TYPEStringThe type of procedure column as defined by the following DatabaseMetaData constants: procedureColumnIn (1), procedureColumnInOut (2), procedureColumnResult (3), procedureColumnOut (4), and procedureColumnReturn (5).
DATA_TYPEintThe data type name as defined in java.sql.Types.
TYPE_NAMEStringThe driver-defined data type name.
PRECISIONintThe number of digits allowed for numeric data.
LENGTHintThe number of characters allowed for character data. The number of digits allowed for numeric data.
SCALEshortThe number of digits to the right of the decimal point in numeric data.
RADIXshortThe radix, or base.
NULLABLEshortWhether the parameter can contain null as defined by the following DatabaseMetaData constants: parameterNoNulls (0), parameterNullable (1), and parameterNullableUnknown (2).
REMARKSStringThe description of the parameter.
COLUMN_DEFStringThe default value for the parameter.
SQL_DATA_TYPEintReserved in the specification.
SQL_DATETIME_SUBintReserved in the specification.
CHAR_OCTET_LENGTHintThe maximum length of binary-based and character-based columns. Null for other data types.
ORDINAL_POSITIONintThe index of the output parameter.
IS_NULLABLEStringWhether the column can include null: YES or NO.
SPECIFIC_NAMEStringThe name that uniquely identifies the stored procedure within its schema.

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7587