SQL Server 2012 stored procedure does not return any result in JDBC

Question

I have a stored procedure in SQL Server 2012 which has a few parameters including table type parameters as well, as SQL server 2012 does not support table type parameters i wrote another stored procedure by setting default values for this table type parameter and having only varchar as parameter for the new procedure

DECLARE @pTempTable TABLE(

   region varchar(max),
   country varchar(max),
   username varchar(max),
   firstname varchar(max),
   lastname varchar(max),
   accessform varchar(max),
   accessmodule varchar(max),
   adate varchar(max),
   atime varchar(max)
 )

 BEGIN

  DECLARE @pTestFromDate DATETIME = '2008/01/01', @pTestToDate      DATETIME     = '2014/12/31'

   DECLARE @pCountryIds GuidsTableType
   INSERT @pCountryIds VALUES(NULL)

   DECLARE @pRegionIds GuidsTableType
   INSERT @pRegionIds VALUES(NULL)

   DECLARE @pUserId UNIQUEIDENTIFIER = NULL
   DECLARE @pModuleId UNIQUEIDENTIFIER = NULL

    insert into @pTempTable exec usp_rpt_UserAudit
   @RoleName = @pRoleName
  ,@pCountryIds=@pCountryIds
  ,@pRegionIds=@pRegionIds
  ,@pUserId=@pUserId
  ,@pModuleId=@pModuleId
  ,@pFromDate=@pTestFromDate
  ,@pToDate=@pTestToDate
  SELECT * from @pTempTable


END

GO

I have written a java code to execute this stored procedure, i am able to establish the connection, but the execute() method always returns false. The java code is as below

    String finalSql = "{ call usp_rpt_sprocexecution(?) } ";
    CallableStatement cs = connection.prepareCall(finalSql);
    cs.setString(1, "Super Administrator");
    System.out.println(cs.execute());
    int c=0;

    while(!((cs.getMoreResults() == false) && (cs.getUpdateCount() <= 1))){
        System.out.println(c++);
        ResultSet rs = cs.getResultSet();
        System.out.println(rs.getString(1));
    }

When i execute this same stored procedure in Management Studio it returns correct result table.

Try adding SET NOCOUNT ON to the start of the procedure. The messages like 1 row(s) affected are being passed back, and probably being interpreted as result sets. This will suppress these messages. — GarethD
– GarethD, Commented Jan 19, 2015 at 12:54

Android Mason · Accepted Answer · 2015-01-19 13:45:13Z

3

The procedure returns messages like 1 row(s) affected which is treated as a Stored Procedure output in java hence subsiding the original result.

The Simple trick was to add SET NOCOUNT ON at the start of the Stored Procedure so that it does not return such messages and hence returns the actual output.

answered Jan 19, 2015 at 13:45

Android Mason

4691 gold badge7 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server 2012 stored procedure does not return any result in JDBC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related