0

Question, Stored procedure, Java code as follows.

In SQL Server Express Edition, Java SE 8.

PROCEDURE1

CREATE PROCEDURE [dbo].[Test1]
AS
DECLARE @UserList TABLE (UserId char(8))
    INSERT @UserList SELECT '00000001'
    SELECT UserId FROM @UserList
    --and SELECT STATEMENT join to @UserList
GO

PROCEDURE2

CREATE PROCEDURE [dbo].[Test1]
AS
    SELECT '00000001'
    --and SELECT STATEMENT join to hard-code '00000001'
GO

Java

try (Connection con = ((DataSource) new InitialContext().lookup("java:comp/env/jdbc/myapp")).getConnection();
    CallableStatement cstmt = con.prepareCall("{call [dbo].[Test1]}");
    ) {

  boolean f = cstmt.execute();
  ResultSet rs = cstmt.getResultSet();

Question

PROCEDURE1 call by jdbc, cstmt.execute() returns false.

PROCEDURE2 call by jdbc, cstmt.execute() returns true. Why?

 

and For What

I want temporary table for dynamic in-clause parameter using by stored procedure.

Otsuki Takaya
  • 162
  • 2
  • 11
  • You either need to add `SET NOCOUNT ON` to prevent the insert from generating an update count (which will be returned **before** the result set), or you need to correctly process all results of a stored procedure; see both duplicates. – Mark Rotteveel Feb 27 '19 at 11:55
  • Thanks for telling me. This question should be closed for duplicate. – Otsuki Takaya Feb 28 '19 at 02:56

0 Answers0