SQLServerException when using StoredProcedureItemReader

Question

I am getting an error when I am trying to use StoredProcedureItemReader.

I am having a stored procedure written in Microsoft SQL server which has the following input and output parameters:
Stored Procedure Name: person_details
Input: @From, @To
Output is a combination of various columns from various tables.

Here is my code:

    StoredProcedureItemReader<ClaimExtractDTO> reader = new StoredProcedureItemReader<>();

    SqlParameter[] parameter = {new SqlParameter("@From", java.sql.Types.BIGINT), new SqlParameter("@To", java.sql.Types.BIGINT)};

    PreparedStatementSetter statementValues = new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setLong(1, minValue); //minValue - Input 1
            ps.setLong(2, maxValue); //maxValue - Input 2
        }
    };  
    reader.setDataSource(dataSource);
    reader.setProcedureName("dbo.person_details");
    reader.setParameters(parameter);
    reader.setPreparedStatementSetter(statementValues);
    reader.setRowMapper(new BeanPropertyRowMapper<>(ClaimExtractDTO.class));    
    return reader;

When running the above code I am getting

Caused by: org.springframework.dao.TransientDataAccessResourceException: Executing stored procedure; SQL [{call dbo.person_details(?, ?)}]; The index 0 of the output parameter is not valid.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The index 0 of the output parameter is not valid.
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:110) ~[spring-jdbc-5.2.7.RELEASE.jar:5.2.7.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.7.RELEASE.jar:5.2.7.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.7.RELEASE.jar:5.2.7.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.7.RELEASE.jar:5.2.7.RELEASE]
    at org.springframework.batch.item.database.StoredProcedureItemReader.openCursor(StoredProcedureItemReader.java:229) ~[spring-batch-infrastructure-4.2.3.BUILD-SNAPSHOT.jar:4.2.3.BUILD-SNAPSHOT]
    at org.springframework.batch.item.database.AbstractCursorItemReader.doOpen(AbstractCursorItemReader.java:428) ~[spring-batch-infrastructure-4.2.3.BUILD-SNAPSHOT.jar:4.2.3.BUILD-SNAPSHOT]
    at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open(AbstractItemCountingItemStreamItemReader.java:150) ~[spring-batch-infrastructure-4.2.3.BUILD-SNAPSHOT.jar:4.2.3.BUILD-SNAPSHOT]
    ... 18 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 0 of the output parameter is not valid.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) ~[mssql-jdbc-7.4.1.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getterGetParam(SQLServerCallableStatement.java:403) ~[mssql-jdbc-7.4.1.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getObject(SQLServerCallableStatement.java:705) ~[mssql-jdbc-7.4.1.jre8.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyCallableStatement.getObject(HikariProxyCallableStatement.java) ~[HikariCP-3.4.5.jar:na]
    at org.springframework.batch.item.database.StoredProcedureItemReader.openCursor(StoredProcedureItemReader.java:222) ~[spring-batch-infrastructure-4.2.3.BUILD-SNAPSHOT.jar:4.2.3.BUILD-SNAPSHOT]
    ... 20 common frames omitted

when I am running the stored procedure on SQL server using exec dbo.person_details '1', '100'; it is running fine and getting me the output which consists of 20 columns from various tables.

I even set set nocount on on the Procedure.

I tried it with SqlParameter[] parameter = {new SqlOutParameter("personKey", java.sql.Types.INTEGER), new SqlParameter("@From", java.sql.Types.BIGINT), new SqlParameter("@To", java.sql.Types.BIGINT)}; thinking there should be outparameters defined. I still get the same error that's mentioned above.

Main contents of the procedure:

USE [employee]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
set nocount on
GO

ALTER PROCEDURE [dbo].[person_details]
@From   BIGINT,
@To     BIGINT
AS
....

Can anyone tell me how I can resolve this issue. Thanks in advance!

Answer 1

You need to ensure you set SET NOCOUNT ON WITHIN the stored procedure itself in order to avoid this error. No output parameters are expected but the rowcount is returned as one.

You are also running the store procedure directly again SQL Server as follows:

exec dbo.person_details '1', '100';

i.e. two input parameters and no output parameters.

Yet in your code you are setting up the parameters as follows:

SqlParameter[] parameter = {new SqlOutParameter("personKey", java.sql.Types.INTEGER), new SqlParameter("@From", java.sql.Types.BIGINT), new SqlParameter("@To", java.sql.Types.BIGINT)};

i.e. one output parameter and two input parameters.